\n\nPairs where either expr1 or expr2 are eliminated first. Then the following\ncalculation is performed:\n\n
\n(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n\n\n\nThe function returns a number, if applied to an empty set NULL is returned.
\nExample:\n
\nSELECT itemid,COVAR_POP(amount,profit) FROM saleitems GROUP BY itemid;\n\n ITEMID COVAR_POP(AMOUNT,PROFIT)\n---------- ------------------------\n 1 84000\n 2 5062.5\n 3 325000\n 4 4111.11111\nAggregate Functions:COVAR_SAMP ( expr1 , expr2 )=Get the sample covariance of a set of pairs.
\n\nPairs where either expr1 or expr2 are eliminated first. Then the following\ncalculation is performed:\n\n
\n(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / (n - 1)\n\n\nThe function returns a number, if applied to an empty set NULL is returned.
\nExample:\n
\nSELECT itemid,COVAR_SAMP(amount,profit) FROM saleitems GROUP BY itemid;\n\n ITEMID COVAR_POP(AMOUNT,PROFIT)\n---------- ------------------------\n 1 84000\n 2 5062.5\n 3 325000\n 4 4111.11111\nAggregate Functions:GROUPING ( expr )=This function is only usefull in select statements with a group by extension such as
ROLLUP\nor CUBE. These function generate extra rows with nulls which is the group by aggregate.\nThis function can be use to distinguish these rows from rows that are actually null.\nThe expr must match a group by expression. If the expr is a null that represent an\naggregate row this function returns 1, otherwise it returns 0.
\nExample:\n
\nSELECT DECODE(GROUPING(itemid),1,'Total',itemid) item,\n SUM(amount) amount,\n SUM(profit) profit\n FROM saleitems\n GROUP BY ROLLUP (itemid);\n\nITEM AMOUNT PROFIT\n----- ------ ------\n1 12000 2200\n2 2760 195\n3 10000 2200\n4 1300 170\nTotal 26060 4765\nAggregate Functions:MAX( expr )=Get the largest value of expr.
\nExample:\n
\nSELECT MAX(amount) FROM saleitems;\n\nMAX(AMOUNT)\n-----------\n 4000\nAggregate Functions:MIN ( expr )=Get the smallest value of expr.
\nExample:\n
\nSELECT MIN(amount) FROM saleitems;\n\nMIN(AMOUNT)\n-----------\n 240\nAggregate Functions:REGR_AVGX ( expr1 , expr2 )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nAVG ( expr2 )\n\nExample:
\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_AVGX ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_AVGX(SYSDATE-SALEDATE,AMOUNT)\n---------- ----------------------------------\n 0 1280\n 8 500\n 10 300\n 13 3333.33333\n 15 1360\n 73 1000\n 74 1400\nAggregate Functions:REGR_AVGY ( expr1 , expr2 )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nAVG ( expr2 )\n\nExample:
\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_AVGY ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_AVGY(SYSDATE-SALEDATE,AMOUNT)\n---------- ----------------------------------\n 0 1.0277662\n 8 60.0277662\n 10 71.0277662\n 13 93.3610995\n 15 107.027766\n 73 514.027766\n 74 518.027766\nAggregate Functions:REGR_COUNT ( expr1 , expr2 )=Pairs where either expr1 or expr2 are eliminated to begin with. This function then returns the number of valid pairs left.
\nExample:
\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_COUNT ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_COUNT(SYSDATE-SALEDATE,AMOUNT)\n---------- -----------------------------------\n 0 3\n 8 2\n 10 1\n 13 3\n 15 2\n 73 1\n 74 3\nAggregate Functions:REGR_INTERCEPT ( expr1 , expr2 )=Calculates the y-intercept of the regression line. Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nAVG ( expr1 ) - REGR_SLOPE ( expr1 , expr2 ) * AVG ( expr2 )\n\nExample:
\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_INTERCEPT ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_INTERCEPT(SYSDATE-SALEDATE,AMOUNT)\n---------- ---------------------------------------\n 0 1.02846065\n 8 60.0284606\n 10\n 13 100.028461\n 15 107.028461\n 73\n 74 518.028461\nAggregate Functions:REGR_R2 ( expr1 , expr2 )=Represent the determination or goodness of fit for the regression. Pairs where either expr1 or expr2 are eliminated to begin with. The value of the functions
VAR_POP ( expr1 ) and VAR_POP ( expr2 ) is evaluated after null pairs are removed. The returned value is then one of the following:\nVAR_POP ( expr2 ) = 0.\nVAR_POP ( expr1 ) = 0 and VAR_POP ( expr2 ) != 0.\nPOWER ( CORR ( expr1 , expr2 ) , 2 ) if VAR_POP ( expr1 ) > 0 and VAR_POP ( expr2 ) != 0.\n\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_R2 ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_R2(SYSDATE-SALEDATE,AMOUNT)\n---------- --------------------------------\n 0 1.4215E-39\n 8 2.8571E-38\n 10\n 13 .25\n 15 1\n 73\n 74 0\nAggregate Functions:REGR_SLOPE ( expr1 , expr2 )=This function returns the slope of the line. Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nCOVAR_POP ( expr1 , expr2 ) / VAR_POP ( expr2 )\n\nExample:
\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_SLOPE ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_SLOPE(SYSDATE-SALEDATE,AMOUNT)\n---------- -----------------------------------\n 0 -3.554E-42\n 8 0\n 10\n 13 -.002\n 15 0\n 73\n 74 0\nAggregate Functions:REGR_SXX ( expr1 , expr2 )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nREGR_COUNT ( expr1 , expr2 ) / VAR_POP ( expr2 )\n\nExample:
\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_SXX ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_SXX(SYSDATE-SALEDATE,AMOUNT)\n---------- ---------------------------------\n 0 4502400\n 8 20000\n 10 0\n 13 666666.667\n 15 819200\n 73 0\n 74 560000\nAggregate Functions:REGR_SXY ( expr1 , expr2 )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nREGR_COUNT ( expr1 , expr2 ) / VAR_POP ( expr1 )\n\nExample:
\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_SXY ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_SXY(SYSDATE-SALEDATE,AMOUNT)\n---------- ---------------------------------\n 0 1.3000E-35\n 8 1.0000E-34\n 10 0\n 13 -1333.3333\n 15 0\n 73 0\n 74 0\nAggregate Functions:REGR_SYY ( expr1 , expr2 )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nREGR_COUNT ( expr1 , expr2 ) / COVAR_POP ( expr1 )\n\nExample:
\n
\nSELECT TRUNC((SYSDATE - saledate) / 7) WEEK,\n REGR_SXY ( SYSDATE - saledate , amount )\n FROM sales , saleitems\n WHERE sales.saleid = saleitems.saleid\n GROUP BY TRUNC((SYSDATE - saledate) / 7);\n\n WEEK REGR_SXY(SYSDATE-SALEDATE,AMOUNT)\n---------- ---------------------------------\n 0 -4.000E-36\n 8 0\n 10 0\n 13 -1333.3333\n 15 0\n 73 0\n 74 0\nAggregate Functions:STDDEV ( DISTINCT|ALL expr )=Get the standard deviation of expr. This function differs from
STDDEV_SAMP in what it returns when there is only one input row. This function returns 0 and STDDEV_SAMP returns null. The standard deviation is the square root of the variance defined for the VARIANCE function.\nExample:\n
\nSELECT AVG(amount),STDDEV(amount) FROM saleitems;\n\nAVG(AMOUNT) STDDEV(AMOUNT)\n----------- --------------\n 1628.75 1225.85412\nAggregate Functions:STDDEV_POP ( expr )=Get the square root of the population variance. This is the same as the square root of the
VAR_POP function.\nExample:\n
\nSELECT STDDEV_POP(profit) FROM saleitems;\n\nSTDDEV_POP(PROFIT)\n------------------\n 277.077205\nAggregate Functions:STDDEV_SAMP ( DISTINCT|ALL expr )=Get the standard deviation of expr. This function differs from
STDDEV in what it returns when there is only one input row. This function returns null and STDDEV returns 0. The standard deviation is the square root of the variance defined for the VARIANCE function.\nExample:\n
\nSELECT AVG(amount),STDDEV_SAMP(amount) FROM saleitems;\n\nAVG(AMOUNT) STDDEV_SAMP(AMOUNT)\n----------- -------------------\n 1628.75 1225.85412\nAggregate Functions:SUM ( DISTINCT|ALL expr )=Get the sum of all expr values. If
DISTINCT is specified only distinct values are calculated.\nExample:\n
\nSELECT SUM(profit) FROM saleitems;\n\nSUM(PROFIT)\n-----------\n 4765\nAggregate Functions:VARIANCE ( DISTINCT|ALL expr )=Get the sample variance of the values in expr, if no values are available null is returned. The following calculation is used.\n
\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n ( COUNT ( expr ) - 1 )\n\nIf applied to an empty set 0 is returned.\nExample:\n
\nSELECT VARIANCE(profit) FROM saleitems;\n\nVARIANCE(PROFIT)\n----------------\n 81889.8958\nAggregate Functions:VAR_POP ( expr )=Get the population variance of the values of expr. This is defined by the following formula.
\n
\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n COUNT ( expr )\n\nExample:\n
\nSELECT VAR_POP(profit) FROM saleitems;\n\nVAR_POP(PROFIT)\n---------------\n 76771.7773\nAggregate Functions:VAR_SAMP ( DISTINCT|ALL expr )=Get the sample variance of the values in expr, if no values are available null is returned. The following calculation is used.\n
\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n ( COUNT ( expr ) - 1 )\n\nIf applied to an empty set null is returned.\nExample:\n
\nSELECT VAR_SAMP(profit) FROM saleitems;\n\nVAR_SAMP(PROFIT)\n----------------\n 81889.8958\nAnalytic Functions:AVG ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Calculates the avarage value of expr.
\nIf DISTINCT is specified duplicates of the same number is only counted as one\nnumber, if ALL or nothing is specified all numbers are counted equally. If DISTINCT is specified you can't use order by and windowing specifications in the analytic_clause.
Analytic Functions:CORR ( expr1 , expr2 ) OVER ( analytic_clause )=Calculate the coefficient of correlation of a set of number pairs.
\n\nPairs where either expr1 or expr2 are eliminated first. Then the following\ncalculation is performed:\n\n
\nCOVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))\n\n\nThe function returns a number, if applied to an empty set NULL is returned. Analytic Functions:COUNT ( *|DISTINCT|ALL expr ) OVER ( analytic_clause )=Return the number of rows in the resultset.
DISTINCT specifies that only\ndistinct values are to be counted. If expr is NULL it is not counted. *\n and ALL indicates that all rows should be counted. If DISTINCT is specified you can't use order by and windowing specifications in the analytic_clause.
Analytic Functions:COVAR_POP ( expr1 , expr2 ) OVER ( analytic_clause )=Get the population covariance of a set of pairs.\n\nPairs where either expr1 or expr2 are eliminated first. Then the following\ncalculation is performed:\n\n
\n(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n\n\n\nThe function returns a number, if applied to an empty set NULL is returned.
\n Analytic Functions:COVAR_SAMP ( expr1 , expr2 ) OVER ( analytic_clause )=Get the sample covariance of a set of pairs.
\n\nPairs where either expr1 or expr2 are eliminated first. Then the following\ncalculation is performed:\n\n
\n(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / (n - 1)\n\n\nThe function returns a number, if applied to an empty set NULL is returned.
\n
Analytic Functions:CUME_DIST ( ) OVER ( analytic_clause )=This computes the relative position of a specified value in a group of values. For a given row the CUME_DIST of this row is the number of rows with lower than or equal to the value of the specified row divided by the total number of rows being evaluated. The values returned are greated than 0 and less than or equal to 1.
Analytic Functions:DENSE_RANK () OVER ( analytic_clause )=This function computes the rank of each row returned from a query in comparison to the other rows in the query based on the expression in the ORDER BY of the analytic_clause. Equal values receive the same rank. The next rank is then added with 1 in comparison to the RANK who adds the number of tied rows.
Analytic Functions:FIRST_VALUE ( expr ) OVER ( analytic_clause )=Returns the first value of expr in an ordered set of values.
Analytic Functions:LAG ( value_expr , offset , default ) OVER ( analytic_clause )=This function gives access to the previous values returned in the query. If offset is specified it indicates the number of rows before the current row to look, the default is 1. The default parameter can be used to specify the values if outside the specified window, the default is null.
Analytic Functions:LAST_VALUE ( expr ) OVER ( analytic_clause )=Returns the last value of expr in an ordered set of values.
Analytic Functions:LEAD ( expr , offset , default ) OVER ( analytic_clause )=This function gives access to future values returned in the query. If offset is specified it indicates the number of rows after the current row to look, the default is 1. The default parameter can be used to specify the values if outside the specified window, the default is null.
Analytic Functions:MAX ( expr ) OVER ( analytic_clause )=Get the largest value of expr.
Analytic Functions:MIN ( expr ) OVER ( analytic_clause )=Get the smallest value of expr.
Analytic Functions:NTILE ( expr ) OVER ( analytic_clause )=This is function divides a dataset into a number of buckets specified by expr and return the number of the bucket for the current row. The number of rows in each bucket can differ at most 1. If expr is larger than the number of rows only rows number of buckets will be filled with 1. Buckets are numbered from 1 to expr.
Analytic Functions:PERCENT_RANK () OVER ( analytic_clause )=This computes the relative position of a specified value in a group of values similar to the CUME_DIST function. The difference is that instead of counting the number of rows before or equal divided by the total number of rows in the set you first deduct one from both the number of rows before and the total number of rows.
Analytic Functions:RANK ( ) OVER ( analytic_clause )=This function computes the rank of each row returned from a query in comparison to the other rows in the query based on the expression in the ORDER BY of the analytic_clause. Equal values receive the same rank. The next rank is then added with the number of rows with this rank value.
Analytic Functions:RATIO_TO_REPORT ( expr ) OVER ( analytic_clause )=This function returns the ratio of expr in comparison to the sum of all expr over the entire set. If expr is null, null is also returned.
Analytic Functions:REGR_AVGX ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n\nAVG ( expr2 )\n
\n
Analytic Functions:REGR_AVGY ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n\nAVG ( expr2 )\n
\n
Analytic Functions:REGR_COUNT ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either expr1 or expr2 are eliminated to begin with. This function then returns the number of valid pairs left.
\n Analytic Functions:REGR_INTERCEPT ( expr1 , expr2 ) OVER ( analytic_clause )=Calculates the y-intercept of the regression line. Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nAVG ( expr1 ) - REGR_SLOPE ( expr1 , expr2 ) * AVG ( expr2 )\n\n Analytic Functions:REGR_R2 ( expr1 , expr2 ) OVER ( analytic_clause )=Represent the determination or goodness of fit for the regression. Pairs where either expr1 or expr2 are eliminated to begin with. The value of the functions
VAR_POP ( expr1 ) and VAR_POP ( expr2 ) is evaluated after null pairs are removed. The returned value is then one of the following:\nVAR_POP ( expr2 ) = 0.\nVAR_POP ( expr1 ) = 0 and VAR_POP ( expr2 ) != 0.\nPOWER ( CORR ( expr1 , expr2 ) , 2 ) if VAR_POP ( expr1 ) > 0 and VAR_POP ( expr2 ) != 0.\n\nCOVAR_POP ( expr1 , expr2 ) / VAR_POP ( expr2 )\n\n Analytic Functions:REGR_SXX ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nREGR_COUNT ( expr1 , expr2 ) / VAR_POP ( expr2 )\n\n Analytic Functions:REGR_SXY ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nREGR_COUNT ( expr1 , expr2 ) / VAR_POP ( expr1 )\n\n Analytic Functions:REGR_SYY ( expr1 , expr2 ) OVER ( analytic_clause )=Pairs where either expr1 or expr2 are eliminated to begin with. Then the following function is calculated:\n
\nREGR_COUNT ( expr1 , expr2 ) / COVAR_POP ( expr1 )\n\n Analytic Functions:ROW_NUMBER ( ) OVER ( analytic_clause )=This function will return a unique value to each row that is returned in the order specify by the
ORDER BY clause of the analytic_clause. The first row returned is 1.
Analytic Functions:STDDEV ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the standard deviation of expr. This function differs from STDDEV_SAMP in what it returns when there is only one input row. This function returns 0 and STDDEV_SAMP returns null. The standard deviation is the square root of the variance defined for the VARIANCE function. If DISTINCT is specified you can't use order by and windowing specifications in the analytic_clause.\n
Analytic Functions:STDDEV_POP ( expr ) OVER ( analytic_clause )=Get the square root of the population variance. This is the same as the square root of the VAR_POP function.
Analytic Functions:STDDEV_SAMP ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the standard deviation of expr. This function differs from STDDEV in what it returns when there is only one input row. This function returns null and STDDEV returns 0. The standard deviation is the square root of the variance defined for the VARIANCE function. If DISTINCT is specified you can't use order by and windowing specifications in the analytic_clause.\n
Analytic Functions:SUM ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the sum of all expr values. If DISTINCT is specified only distinct values are calculated. If DISTINCT is specified you can't use order by and windowing specifications in the analytic_clause.
Analytic Functions:VARIANCE ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the sample variance of the values in expr, if no values are available null is returned. The following calculation is used.\n\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n ( COUNT ( expr ) - 1 )\n\nIf applied to an empty set 0 is returned. If
DISTINCT is specified you can't use order by and windowing specifications in the analytic_clause.
Analytic Functions:VAR_POP ( expr ) OVER ( analytic_clause )=Get the population variance of the values of expr. This is defined by the following formula.\n
\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n COUNT ( expr )\n\n Analytic Functions:VAR_SAMP ( DISTINCT|ALL expr ) OVER ( analytic_clause )=Get the sample variance of the values in expr, if no values are available null is returned. The following calculation is used.\n
\n( SUM ( expr * expr ) - SUM ( expr ) * SUM ( expr ) / COUNT ( expr ) ) / \n ( COUNT ( expr ) - 1 )\n\nIf applied to an empty set null is returned. If
DISTINCT is specified you can't use order by and windowing specifications in the analytic_clause.
Char to Char Functions:CHR ( n USING NCHAR_CS )=Get the character specified by the binary equivalent of n in the national character set. If USING NCHAR_CS is used, this function returns the equivalent of the national NVARCHAR2. Otherwise the VARCHAR2 character set is user.\nExamples:\n
\nSELECT CHR ( 65 ) || CHR ( 66 ) || CHR ( 67 ) abc FROM DUAL;\n\nABC\n---\nABC\nChar to Char Functions:CONCAT ( char1 , char2 )=This function concatenates the char1 string with char2. Which means it will perform the same task as the operator
||.\nExamples:\n
\nSELECT CONCAT ( 'Foo' , 'bar' ) FROM DUAL;\n\nCONCAT\n------\nFoobar\nChar to Char Functions:INITCAP ( char )=Return the string of char with the first character in uppercase and all the rest in lowercase.
\nExamples:\n
\nSELECT INITCAP ( 'MaUrItZ' ) FROM DUAL;\n\nINITCAP\n-------\nMauritz\nChar to Char Functions:LOWER ( char )=Returns the string char with all letters in lowercase.
\nExamples:\n
\nSELECT LOWER ( 'FoPPa' ) FROM DUAL;\n\nLOWER\n-----\nfoppa\nChar to Char Functions:LPAD ( char1 , n , char2 )=This function returns char1 padded from the left to n number of characters. If char2 is specified this is used for padding instead of space which is the default.
\nExamples:\n
\nSELECT LPAD ( '>Padding' , 12 , '-=' ) FROM DUAL;\n\nLPAD('>PADDI\n------------\n-=-=>Padding\n
Char to Char Functions:LTRIM ( char1 , char2 )=Removes all characters from the char1 available in the set defined by the characters available in the string char2 from the left until a character not in the set is encountered.\nExamples:\n
\nSELECT LTRIM ( '-=-=-=->Text' , '-=>' ) FROM DUAL;\n\nLTRI\n----\nText\nChar to Char Functions:NLSSORT ( char , nlsparam )=This function returns a string used for sorting the char. The value of nlsparam has the form
'NLS_param = value' usually 'NLS_SORT = sort' where sort can either be BINARY or any other sort specifier.\nExamples:\n
\nSELECT NLSSORT ( 'ABCÅÄÖ' , 'NLS_SORT = Swedish' ) FROM DUAL;\n\nNLSSORT('ABCÅÄÖ','NLS_SORT=SWEDISH')\n----------------------------------------------------\n14191E898A8B0001010101010100\n
Char to Char Functions:NLS_INITCAP ( char , nlsparam )=This function returns the string char with the first character is made uppercase and all the other lowercase. The value of nlsparam has the form 'NLS_param = value' usually 'NLS_SORT = sort' where sort can either be BINARY or any other sort specifier.\nExamples:\n
\nSELECT NLS_INITCAP ( 'ABCÅÄÖ' , 'NLS_SORT = Swedish' ) FROM DUAL;\n\nNLS_IN\n------\nAbcåäö\nChar to Char Functions:NLS_LOWER ( char , nlsparam )=This function returns the string char with all letters converted to lowercase. The value of nlsparam has the form
'NLS_param = value' usually 'NLS_SORT = sort' where sort can either be BINARY or any other sort specifier.\nExamples:\n
\nSELECT NLS_LOWER ( 'ABCÅÄÖ' , 'NLS_SORT = Swedish' ) FROM DUAL;\n\nNLS_LO\n------\nabcåäö\nChar to Char Functions:NLS_UPPER ( char , nlsparam )=This function returns the string char with all letters converted to uppercase. The value of nlsparam has the form
'NLS_param = value' usually 'NLS_SORT = sort' where sort can either be BINARY or any other sort specifier.\nExamples:\n
\nSELECT NLS_UPPER ( 'ABCÅÄÖ' , 'NLS_SORT = Swedish' ) FROM DUAL;\n\nNLS_UP\n------\nABCÅÄÖ\nChar to Char Functions:REPLACE ( char , search , replace )=This function will return a string where every occurance in char of the string search is replaced with the replace string.
\nExample:\n
\nSELECT REPLACE ('Gnu','Gnu','Gnu Is Not Unix') FROM DUAL;\n\nREPLACE('GNU','\n---------------\nGnu Is Not Unix\n
Char to Char Functions:RPAD ( char1 , n , char2 )=This function returns char1 padded from the right to n number of characters. If char2 is specified this is used for padding instead of space which is the default.\nExamples:\n
\nSELECT RPAD ( 'Padding<' , 12 , '=-' ) FROM DUAL;\n\nRPAD('PADDIN\n------------\nPadding<=-=-\n
Char to Char Functions:RTRIM ( char1 , char2 )=Removes all characters from the char1 available in the set defined by the characters available in the string char2 from the right until a character not in the set is encountered.\nExamples:\n
\nSELECT RTRIM ( 'Text<=-=-=-' , '<-=' ) FROM DUAL;\n\nRTRI\n----\nText\nChar to Char Functions:SOUNDEX ( char )=Returns a string that contains a phonetic representation of char. This is usefull for comparing strings that sound similarly, but are spelled different. The algorithm used is available in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth.
\nExamples:\n
\nSELECT 'Johnson sounds like Jonsson'\n FROM DUAL WHERE SOUNDEX ( 'Johnson' ) = SOUNDEX ( 'Jonsson' );\n\n'JOHNSONSOUNDSLIKEJONSSON'\n---------------------------\nJohnson sounds like Jonsson\nChar to Char Functions:SUBSTR ( char , m , n )=This function returns a substring of of the char string. If m is 0 it is treated as 1 which means the beginning of the string, 2 meaning the second character etc. If m is negative it will count from the end of the string with -1 meaning the last character of the string. If n is not specified the rest of the string is returned.
\nExamples:\n
\nSELECT SUBSTR('Henrik P Johnson',8,3) FROM DUAL;\n\nSUB\n---\nP J\n
Char to Char Functions:SUBSTRB ( char , m , n )=This function returns a substring of of the char string. The difference from SUBSTR is that this function counts bytes instead of characters which differs if you use multichar charactersets.\nExamples:
\nIf run on a UNICODE characterset database.\n
\nSELECT SUBSTRB('Henrik P Johnson',7,4) FROM DUAL;\n\nSU\n--\nri\n
Char to Char Functions:TRANSLATE ( char , from , to )=This function will return a string where every occurance of a character in char is replaced from the character in from with the corresponding character in to. If the character is not available in to it is removed from the result.\nExample:\n
\nSELECT TRANSLATE ( 'Mauritz' , 'Mauritz' , 'Henrik' ) FROM DUAL;\n\nTRANSL\n------\nHenrik\nChar to Char Functions:TRIM ( LEADING|TRAILING|BOTH trim FROM source )=This function can trim characters from both beginning (If
LEADING or BOTH is specified) and end (If TRAILING or BOTH) with BOTH being the default. If trim isn't specified any whitespace is the default. Any characyers in the trim set will be removed from the specified ends of source.\nExamples:\n
\nSELECT TRIM ( '-=-=-=>Text<=-=-=-' , '<-=>' ) FROM DUAL;\n\nTRIM\n----\nText\nChar to Char Functions:UPPER ( char )=Returns the string char with all letters in uppercase.
\nExamples:\n
\nSELECT UPPER ( 'FoPPa' ) FROM DUAL;\n\nUPPER\n-----\nFOPPA\nChar to Number Functions:ASCII ( char )=Get the number representation of the first character in char.
\nExample:\n
\nSELECT ASCII('Aloha') FROM DUAL;\n\nASCII('ALOHA')\n--------------\n 65\n
Char to Number Functions:INSTR ( string , substring , pos , occurance )=Search for substring in string. If pos is specified it indicates the character in string to start searching, if negative Oracle will search backwards in the string. If occurance is specified it indicates how many hits to discard before returning (1 meaning the first match). The function returns the position of the charater where the match begins in string.\nExample:\n
\nSELECT INSTR('excellence','e',3,2) FROM DUAL;\n\nINSTR('EXCELLENCE','E',3,2)\n---------------------------\n 7\n
Char to Number Functions:INSTRB ( str , substring , pos , occurance )=This function is the same as INSTR except that pos and the returned number is in bytes instead of characters.
Char to Number Functions:LENGTH ( char )=Get the length of char in characters.\nExample:\n
\nSELECT LENGTH('GlobeCom') FROM DUAL;\n\nLENGTH('GLOBECOM')\n------------------\n 8\n
Char to Number Functions:LENGTHB ( char )=Get the length of char in bytes.\nExample:
\nIf performed on a UNICODE database.\n
\nSELECT LENGTHB('GlobeCom') FROM DUAL;\n\nLENGTHB('GLOBECOM')\n-------------------\n 16\n
Conversion Functions:CHARTOROWID ( char )=Convert a char str string value to a ROWID. This is the reverse of the ROWIDTOCHAR function.
Conversion Functions:CONVERT ( char , dest_char_set , source_char_set )=Convert the char string from the source_char_set or the database character set to the dest_char_set. Observe that TOra always communicate with the database in the UTF-8 character set.\nExample:\nAssumes a Latin-1 character set database.\n
\nSELECT CONVERT ( 'ÅÄÖåäö' , 'UTF8' ) FROM DUAL;\n\nCONVERT('ÅÄÖ\n------------\nÃ\nÃ\n Ãåäö\n
Conversion Functions:HEXTORAW ( char )=Convert a string char containing a hexadecimal dump of RAW data. This is the reverse of the RAWTOHEX function.
Conversion Functions:NUMTODSINTERVAL ( n , interval )=Converts n to an INTERVAL DAY TO SECOND literal. The string interval specifies the unit of the value n and can be one of the following 'DAY', 'HOUR', 'MINUTE' and 'SECOND'.
Conversion Functions:NUMTOYMINTERVAL ( n , interval )=Converts n to an INTERVAL YEAR TO MONTH literal. The string interval specifies the unit of the value n and can be one of the following 'YEAR' and 'MONTH'.
Conversion Functions:RAWTOHEX ( char )=This function converts the RAW value char to a hexadecimal representation of it that can be treated like a string. This is the reverse of the HEXTORAW function.\nExample:\n
\nSELECT RAWTOHEX ( 'GlobeCom' ) FROM DUAL;\n\nRAWTOHEX('GLOBEC\n----------------\n476C6F6265436F6D\n
Conversion Functions:ROWIDTOCHAR ( rowid )=This function returns a string representation of a ROWID. Use the CHARTOROWID to convert the string back to a ROWID.
Conversion Functions:TO_CHAR( val , fmt , nlsparam )=Convert the val to a string the format specified by the optional fmt parameter and any NLS parameters can be modified by nlsparam. val can be either of date or number. For more information about date and number conversion specifiers refere to the Oracle SQL Reference manual.\nExample:\n
\nSELECT TO_CHAR ( 123 , '99999.000' ) FROM DUAL;\n\nTO_CHAR(12\n----------\n 123.000\nConversion Functions:TO_DATE ( char , fmt , nlsparam )=Convert the string char to a date using the format specified by the optional fmt parameter and any NLS parameters can be modified by nlsparam. For more information about date conversion format specifiers refer to the Oracle SQL Reference manual.
\nExample:\n
\nSELECT TO_DATE ( '2000-01-01' , 'YYYY-MM-DD' ) FROM DUAL;\n\nTO_DATE('\n---------\n01-JAN-00\n
Conversion Functions:TO_LOB ( long )=This function converts a long value of LONG or LONG RAW format to a LOB object. This can only be used in a SELECT list of a subquery or in an INSERT statement.
Conversion Functions:TO_MULTI_BYTE ( char )=This function converts a singlebyte string char to its corresponding multibyte string. If no equivalent multibyte character is available the singlestring character will appear in the returned instead.
Conversion Functions:TO_NUMBER ( char , fmt , nlsparam )=Convert the string char to a number using the format specified by the optional fmt parameter and any NLS parameters can be modified by nlsparam. For more information about number conversion format specifiers refer to the Oracle SQL Reference manual.Conversion Functions:TO_SINGLE_BYTE ( char )=This function converts a multibyte character string char to its corresponding singlebyte characters. If no equivalent singlebyte character is available the multibyte character will appear in the returned instead.
\n
Conversion Functions:TRANSLATE ( text USING CHAR_CS )=Converts text to the character set specified for conversion between the database characterset and the national character set. Specifying CHAR_CS will return a VARCHAR2 string, NCHAR_CS will return a NVARCHAR2 string.
Date Functions:ADD_MONTHS ( d , n )=Add n months to the date d. If next resulting month has less days than the day of the month in d the last of that month will be returned, otherwise the result will have the same day of month as d.\n
Date Functions:LAST_DAY ( d )=Get the last of the month of the date d.
\nExample:
\nWill return number of days left in month.\n
\nSELECT TRUNC ( LAST_DAY ( SYSDATE ) - SYSDATE ) FROM DUAL;\n\nTRUNC(SYSDATE-LAST_DAY(SYSDATE))\n--------------------------------\n 27\nDate Functions:MONTHS_BETWEEN ( d1 , d2 )=Will calculate the number of months between the date d1 and d2. If d1 is later than d2 the result will be positive, otherwise it will be negative. The fraction is calculated based on a 31-day month.
\nExample:\n
\nSELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('1973-12-21','YYYY-MM-DD')) FROM DUAL;\n\nMONTHS_BETWEEN(SYSDATE,TO_DATE('1973-12-21','YYYY-MM-DD'))\n----------------------------------------------------------\n 329.437957\n
Date Functions:NEW_TIME ( d , z1 , z2 )=This function converts the date d specified in timezone z1 to what the time is in the timezone z2. The timezones can be one of the following AST, ADT, BST,\nBDT, CST, CDT, EST, EDT, GMT, HST, HDT, MST, MDT, NST, PST, PDT, YST and YDT. For more information refer to the Oracle SQL Reference manual.\nExample:\n
\nSELECT TO_CHAR ( NEW_TIME ( SYSDATE , 'CDT' , 'GMT' ) , \n 'YYYY-MM-DD HH24:MI:SS' )\n FROM DUAL;\n\nTO_CHAR(NEW_TIME(SY\n-------------------\n2001-06-03 18:59:03\nDate Functions:NEXT_DAY ( d , char )=Get the date of the first weekday named by char after the specified date d. char must be in the language of your session and can be either the full name or it's abbreviation.
\nExample:\n
\nSELECT NEXT_DAY ( SYSDATE , 'MONDAY' ) FROM DUAL;\n\nNEXT_DAY(\n---------\n04-JUN-01\nDate Functions:ROUND ( d , fmt )=Round off the date d to the unit specified by the fmt format model. For more information about date formats refer to the Oracle SQL Reference manual. If fmt is not specified the result is rounded to days.
\nExample:\n
\nSELECT ROUND ( SYSDATE , 'MONTH' ) FROM DUAL;\n\nROUND(SYS\n---------\n01-JUN-01\nDate Functions:SYSDATE=Get the current date and time of the database. Date Functions:TRUNC ( d , char )=Truncate the date d to the unit specified by the fmt format model. For more information about date formats refer to the Oracle SQL Reference manual. If fmt is not specified the result is rounded to days.
\nExample:\n
\nSELECT TRUNC ( SYSDATE , 'MONTH' ) FROM DUAL;\n\nTRUNC(SYS\n---------\n01-JUN-01\nExample Tables=The following SQL script will create the example tables used in these template descriptions.
\n
\nMisc Single-Row Functions:BFILENAME ( ' directory ' , ' filename ' )= Misc Single-Row Functions:DUMP ( expr , return_fmt , start , length )=Get a
\nPROMPT CREATE TABLE saleitems\n\nCREATE TABLE saleitems\n(\n saleid NUMBER NOT NULL\n , itemid NUMBER NOT NULL\n , quantity NUMBER \n , amount NUMBER \n , profit NUMBER \n);\n\nPROMPT ALTER TABLE saleitems ADD CONSTRAINT saleitems_pk PRIMARY KEY\n\nALTER TABLE saleitems ADD CONSTRAINT saleitems_pk PRIMARY KEY\n(\n saleid,\n itemid\n);\n\nPROMPT CREATE TABLE sales\n\nCREATE TABLE sales\n(\n saleid NUMBER NOT NULL\n , customerid NUMBER \n , saledate DATE \n , deliverydate DATE \n , sellerid NUMBER \n)\n;\n\nPROMPT ALTER TABLE sales ADD CONSTRAINT sales_pk PRIMARY KEY\n\nALTER TABLE sales ADD CONSTRAINT sales_pk PRIMARY KEY\n(\n saleid\n);\n\nPROMPT CONTENTS OF saleitems\n\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('1','1','1','1000','200');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('1','2','10','1200','60');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('1','3','2','2000','400');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('2','2','2','240','5');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('2','3','3','3000','600');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('2','4','6','600','100');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('3','3','1','1000','200');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('4','2','6','720','30');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('4','1','2','2000','500');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('5','3','4','4000','1000');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('5','1','3','3000','600');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('6','2','5','600','100');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('6','4','4','400','50');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('7','4','3','300','20');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('8','1','3','3000','400');\nINSERT INTO saleitems (saleid,itemid,quantity,amount,profit) VALUES ('9','1','3','3000','500');\nCOMMIT;\n\nPROMPT CONTENTS OF sales\n\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('1','1',TO_DATE('2000-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2000-01-05 00:00:00','YYYY-MM-DD HH24:MI:SS'),'1');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('2','1',TO_DATE('2001-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),NULL,'2');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('3','2',TO_DATE('2000-01-05 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2000-01-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),'3');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('4','2',TO_DATE('2001-02-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-02-24 00:00:00','YYYY-MM-DD HH24:MI:SS'),'2');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('5','3',TO_DATE('2001-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-03-19 00:00:00','YYYY-MM-DD HH24:MI:SS'),'2');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('6','3',TO_DATE('2001-04-03 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-04-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),'3');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('7','2',TO_DATE('2001-03-23 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-04-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),'3');\nINSERT INTO sales (saleid,customerid,saledate,deliverydate,sellerid) VALUES ('8','1',TO_DATE('2001-02-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2001-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),'1');\nCOMMIT;\n\n
VARCHAR2 containing the internal database representation of expr. Data is always always in the database character set. return_fmt can be used to specify the format of the returned data and specifies the radix (Octal, decimal and hexadecimal available) or 17 which specifies single characters. If 1000 is added to the return_fmt will also return the character set information of the data. start and length can be used to extract different portions of the internal data.\nExample:\n
\nSELECT DUMP ( 'GlobeCom' ) FROM DUAL;\n\nDUMP('GLOBECOM')\n------------------------------------------\nTyp=96 Len=8: 71,108,111,98,101,67,111,109\n\nSELECT DUMP ( 'Mauritz' , 1017 ) FROM DUAL;\n\nDUMP('MAURITZ',1017)\n-----------------------------------------------------\nTyp=96 Len=7 CharacterSet=WE8ISO8859P1: M,a,u,r,i,t,z\n
Misc Single-Row Functions:EMPTY_BLOB ( )=Return an empty but initialised BLOB object.Misc Single-Row Functions:EMPTY_CLOB ( )=Return an empty but initialised BLOB object.
Misc Single-Row Functions:GREATEST ( expr1 , expr2 )=Returns the greatest of the specified expressions.
\nExample:\n
\nSELECT GREATEST ( 1 , 5 , 3 , 8 , -5 ) FROM DUAL;\n\nGREATEST(1,5,3,8,-5)\n--------------------\n 8\nMisc Single-Row Functions:LEAST ( expr1 , expr2 )=Returns the smallest of the specified expressions.
\nExample:\n
\nSELECT LEAST ( 1 , 5 , 3 , 8 , -5 ) FROM DUAL;\n\nLEAST(1,5,3,8,-5)\n-----------------\n -5\nMisc Single-Row Functions:NLS_CHARSET_DECL_LEN ( bytes , csid )=Get the declaration width of an
NCHAR in number of characters. The bytes specifies the width of the column in bytes. The csid indicates the character set ID.\nExample:\n
\nSELECT NLS_CHARSET_DECL_LEN ( 200 ,\n NLS_CHARSET_ID ( 'ZHT16BIG5FIXED' ) )\n FROM DUAL;\n\nNLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('ZHT16BIG5FIXED'))\n----------------------------------------------------------\n 100\n
Misc Single-Row Functions:NLS_CHARSET_ID ( charset )=Get the character set ID from an NLS character set name charset. If 'NCHAR_CS' is specified the multibyte character set of the server and 'CHAR_CS' the singlebyte character set of the server.\nExample:\n
\nSELECT NLS_CHARSET_ID ( 'ZHT16BIG5FIXED' ) FROM DUAL;\n\nNLS_CHARSET_ID('ZHT16BIG5FIXED')\n--------------------------------\n 1865\n
Misc Single-Row Functions:NLS_CHARSET_NAME ( n )=Get the character set name from a character set ID.\nExample:\n
\nSELECT NLS_CHARSET_NAME ( 1865 ) FROM DUAL;\n\nNLS_CHARSET_NA\n--------------\nZHT16BIG5FIXED\nMisc Single-Row Functions:NVL ( expr1 , expr2 )=This function returns expr1 if it isn't null, in that case expr2 is returned.
\nExample:\n
\nSELECT NVL ( NULL , 'GlobeCom' ) , NVL ( 'Rules' , 'Sucks' ) FROM DUAL;\n\nNVL(NULL NVL('\n-------- -----\nGlobeCom Rules\n
Misc Single-Row Functions:NVL2 ( expr1 , expr2 , expr3 )=This function returns expr2 if expr1 is not null, and expr3 if it is null.\nExample:\n
\nSELECT NVL2 ( 1 , 'Mauritz' , 'Henrik' ) FROM DUAL;\n\nNVL2(1,\n-------\nMauritz\nMisc Single-Row Functions:SYS_CONTEXT ( namespace , attribute , length )=This function returns the value of attribute associated with the context namespace. The maximum size is by default 256 bytes which can be overridden by specifying the length parameter. For more information see the Oracle documentation.
\nExample:\n
\nSELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') \n FROM DUAL;\n\nSYS_CONTEXT('USERENV','SESSION_USER')\n--------------------------------------------------------------------------------\nSYSTEM\n
Misc Single-Row Functions:SYS_GUID ( )=This function returns a generated globally unique number of format RAW consisting of 16 bytes.\nExample:\n
\nSELECT SYS_GUID ( ) FROM DUAL;\n\nSYS_GUID()\n--------------------------------\n85DB35952D9A27E7E030007F010040E7\nMisc Single-Row Functions:UID=This function returns an integer that identifies the session user.
\nExample:\n
\nSELECT UID FROM DUAL;\n\n UID\n----------\n 5\nMisc Single-Row Functions:USER=Return the name of the session user.
\nExample:\n
\nSELECT USER FROM DUAL;\n\nUSER\n------------------------------\nSYSTEM\nMisc Single-Row Functions:USERENV ( option )=This function returns information about the current session. For more information about values of option refer to the Oracle documentation.
\nExample:\n
\nSELECT USERENV ( 'SESSIONID' ) FROM DUAL;\n\nUSERENV('SESSIONID')\n--------------------\n 13809\n
Misc Single-Row Functions:VSIZE ( expr )=Get the number of bytes needed to store the value of expr in the internal database representation.\nExample:\n
\nSELECT VSIZE ( 'Mauritz' ) FROM DUAL;\n\nVSIZE('MAURITZ')\n----------------\n 7\n
Number functions:ABS ( n )=ABS returns the absolute value of n.\nExample:
\n
\nSELECT ABS(-32) FROM DUAL;\n\nABS(-32)\n--------\n 32\nNumber functions:ACOS ( n )=Get the arc cosine of n. The angle unit is radeans.
\nExample:\n
\nSELECT ACOS ( 0.5 ) FROM DUAL;\n\n ACOS(0.5)\n----------\n1.04719755\nNumber functions:ADD_MONTHS ( d , n )=Add n months to the date d. If next resulting month has less days than the day of the month in d the last of that month will be returned, otherwise the result will have the same day of month as d.\n Number functions:ASIN ( n )=Get the arc sine of n. The angle unit is radeans.
\nExample:\n
\nSELECT ASIN ( 0.5 ) FROM DUAL;\n\n ASIN(0.5)\n----------\n.523598776\nNumber functions:ATAN ( n )=Get the arc tangentent of n. The angle unit is radeans.
\nExample:\n
\nSELECT ATAN ( 0.5 ) FROM DUAL;\n\n ATAN(0.5)\n----------\n.463647609\nNumber functions:ATAN2 ( n , m )=Get the arc tangentent of n / m. The angle unit is radeans.
\nExample:\n
\nSELECT ATAN2 ( 1 , 2 ) FROM DUAL;\n\nATAN2(1,2)\n----------\n.463647609\nNumber functions:BITAND ( argument1 , argument2 )=Calculate the bitwise and of argument1 and argument2 which must both be positive integers. Number functions:CEIL ( n )=Return the smallest integer greater than or equal to n.
\nExample:\n
\nSELECT CEIL ( 1.3 ) FROM DUAL;\n\n CEIL(1.3)\n----------\n 2\nNumber functions:COS ( n )=Return the cosine of n. The angle unit is radeans.
\nExample:\n
\nSELECT COS ( ACOS ( 0.5 ) ) FROM DUAL;\n\nCOS(ACOS(0.5))\n--------------\n .5\nNumber functions:COSH ( n )=Return the hyperbolic cosine of n.
\nExample:\n
\nSELECT COSH ( 1 ) FROM DUAL;\n\n COSH(1)\n----------\n1.54308063\nNumber functions:EXP ( n )=Calculate e raised to the n:th power.
\nExample:\n
\nSELECT EXP( 1 ) FROM DUAL;\n\n EXP(1)\n----------\n2.71828183\nNumber functions:FLOOR ( n )=Return the largest integer smaller than or equal to n.
\nExample:\n
\nSELECT FLOOR ( 1.3 ) FROM DUAL;\n\nFLOOR(1.3)\n----------\n 1\nNumber functions:LN ( n )=Calculate the natural logaritm of n.
\nExample:\n
\nSELECT LN ( EXP ( 1 ) ) FROM DUAL;\n\nLN(EXP(1))\n----------\n 1\nNumber functions:LOG ( m , n )=Calculate the logarithm with base m of n.
\nExample:\n
\nSELECT LOG ( 10 , 100 ) FROM DUAL;\n\nLOG(10,100)\n-----------\n 2\nNumber functions:MOD ( m , n )=Calculate the remainder of m divided by n.
\nExample:\n
\nSELECT MOD ( 15 , 4 ) , MOD ( -14 , 4 ) FROM DUAL;\n\n MOD(15,4) MOD(-14,4)\n---------- ----------\n 3 -2\nNumber functions:POWER ( m , n )=Calculate m raised to the power of n.
\nExample:\n
\nSELECT POWER(4,0.5) FROM DUAL;\n\nPOWER(4,0.5)\n------------\n 2\nNumber functions:ROUND ( n , m )=Rounds the number n off to m places right of the decimal. If m is not specified n is rounded to an integer.
\nExample:\n
\nSELECT ROUND ( 123 , -1 ) FROM DUAL;\n\nROUND(123,-1)\n-------------\n 120\nNumber functions:SIGN ( n )=Return the sign of n. This means -1 is returned if n is below 0, 0 if it is equal to 0 and 1 if above.
\nExample:\n
\nSELECT SIGN(12) FROM DUAL;\n\n SIGN(12)\n----------\n 1\nNumber functions:SIN ( n )=Return the sine of n. The angle unit is radeans.
\nExample:\n
\nSELECT SIN ( ASIN ( 0.5 ) ) FROM DUAL;\n\nSIN(ASIN(0.5))\n--------------\n .5\nNumber functions:SINH ( n )=Return the hyperbolic sine of n.
\nExample:\n
\nSELECT SINH ( 1 ) FROM DUAL;\n\n SINH(1)\n----------\n1.17520119\nNumber functions:SQRT ( n )=Calculate the square root of n.
\nExample:\n
\nSELECT SQRT(100) FROM DUAL;\n\n SQRT(100)\n----------\n 10\nNumber functions:TAN ( n )=Return the tangent of n. The angle unit is radeans.
\nExample:\n
\nSELECT TAN ( ATAN ( 0.5 ) ) FROM DUAL;\n\nTAN(ATAN(0.5))\n--------------\n .5\nNumber functions:TANH ( n )=Return the hyperbolic tangent of n.
\nExample:\n
\nSELECT TANH ( 1 ) FROM DUAL;\n\n TANH(1)\n----------\n.761594156\nNumber functions:TRUNC ( n , m )=Rounds the number n off to m places right of the decimal downwards. If m is not specified n is rounded to an integer.
\nExample:\n
\nSELECT TRUNC ( 123 , -1 ) FROM DUAL;\n\nTRUNC(123,-1)\n-------------\n 120 Object Functions:DEREF ( expr )=Returns an object reference to the object of expr. Object Functions:MAKE_REF ( table|view , key )=Create a referens to a row of view or table. Object Functions:REF ( correlation )=See Oracle SQL Reference for more information about this function. Object Functions:REFTOHEX ( expr )=Convert a reference expr to a hexadecimal value. Object Functions:VALUE ( correlation )=See Oracle SQL Reference for more information about this function.