Transact-SQL User's Guide

SQL Functions

A SQL function is similar to an operator in that it manipulates data items and returns a result. SQL functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments:

function(argument, argument, ...)

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. See "Data Conversion".

If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not follow this rule are CONCAT, DECODE, DUMP, NVL, and REPLACE.

SQL functions should not be confused with user functions written in PL/SQL. User functions are described in "User Functions".

In the syntax diagrams for SQL functions, arguments are indicated with their datatypes following the conventions described in "Syntax Diagrams and Notation" in the Preface of this reference.

SQL functions are of these general types:

single-row (or scalar) functions
group (or aggregate) functions

The two types of SQL functions differ in the number of rows upon which they act. A single-row function returns a single result row for every row of a queried table or view; a group function returns a single result row for a group of queried rows.

Single-row functions can appear in select lists (if the SELECT statement does not contain a GROUP BY clause), WHERE clauses, START WITH clauses, and CONNECT BY clauses.

Group functions can appear in select lists and HAVING clauses. If you use the GROUP BY clause in a SELECT statement, Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, all elements of the select list must be expressions from the GROUP BY clause, expressions containing group functions, or constants. Oracle applies the group functions in the select list to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, Oracle applies group functions in the select list to all the rows in the queried table or view. You use group functions in the HAVING clause to eliminate groups from the output based on the results of the group functions, rather than on the values of the individual rows of the queried table or view. For more information on the GROUP BY and HAVING clauses, see the GROUP BY Clause and the HAVING Clause.

In the sections that follow, functions are grouped by the datatypes of their arguments and return values.

Number Functions

Number functions accept numeric input and return numeric values. This section lists the SQL number functions. Most of these functions return values that are accurate to 38 decimal digits. The transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits.

ATAN2

Purpose

Returns the arc tangent of n and m. Inputs are in an unbounded range, and outputs are in the range of - to , depending on the signs of n and m, and are expressed in radians. Atan2(n,m) is the same as atan2(n/m)

Example

SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL;Arc_Tangent2------------.982793723

CEIL

Purpose

Returns smallest integer greater than or equal to n.

Example

SELECT CEIL(15.7) "Ceiling" FROM DUAL; Ceiling----------16

COS

Purpose

Returns the cosine of n (an angle expressed in radians).

Example

SELECT COS(180 * 3.14159265359/180)"Cosine of 180 degrees" FROM DUAL; Cosine of 180 degrees----------------------1

COSH

Purpose

Returns the hyperbolic cosine of n.

Example

SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL;Hyperbolic cosine of 0----------------------1

EXP

Purpose

Returns e raised to the nth power; e = 2.71828183 ...

Example

SELECT EXP(4) "e to the 4th power" FROM DUAL; e to the 4th power------------------54.59815

FLOOR

Purpose

Returns largest integer equal to or less than n.

Example

SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor----------15

LN

Purpose

Returns the natural logarithm of n, where n is greater than 0.

Example

SELECT LN(95) "Natural log of 95" FROM DUAL; Natural log of 95-----------------4.55387689

LOG

Purpose

Returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number.

Example

SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL; Log base 10 of 100------------------2

MOD

Syntax

MOD(m,n)

Purpose

Returns remainder of m divided by n. Returns m if n is 0.

Example

SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus----------3

This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:

m - n * FLOOR(m/n)

The following statement illustrates the difference between the MOD function and the classical modulus:

SELECT m, n, MOD(m, n),m - n * FLOOR(m/n) "Classical Modulus"FROM test_mod_table; M N MOD(M,N) Classical Modulus---------- ---------- ---------- -----------------11 4 3 311 -4 3 -1-11 4 -3 1-11 -4 -3 -3

POWER

Purpose

Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.

Example

SELECT POWER(3,2) "Raised" FROM DUAL; Raised----------9

ROUND

Syntax

ROUND(n[,m])

Purpose

Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

Example 1

SELECT ROUND(15.193,1) "Round" FROM DUAL; Round----------15.2

Example 2

SELECT ROUND(15.193,-1) "Round" FROM DUAL; Round----------20

SIGN

Syntax

SIGN(n)

Purpose

If n<0, the function returns -1; if n=0, the function returns 0; if n>0, the function returns 1.

Example

SELECT SIGN(-15) "Sign" FROM DUAL; Sign-----------1

SIN

Purpose

Returns the sine of n (an angle expressed in radians).

Example

SELECT SIN(30 * 3.14159265359/180)"Sine of 30 degrees" FROM DUAL; Sine of 30 degrees------------------.5

SINH

Purpose

Returns the hyperbolic sine of n.

Example

SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL; Hyperbolic sine of 1--------------------1.17520119

SQRT

Purpose

Returns square root of n. The value n cannot be negative. SQRT returns a "real" result.

Example

SELECT SQRT(26) "Square root" FROM DUAL; Square root-----------

5.09901951

TAN

Purpose

Returns the tangent of n (an angle expressed in radians).

Example

SELECT TAN(135 * 3.14159265359/180)"Tangent of 135 degrees" FROM DUAL; Tangent of 135 degrees----------------------- 1

TANH

Purpose

Returns the hyperbolic tangent of n.

Example

SELECT TANH(.5) "Hyperbolic tangent of .5"FROM DUAL; Hyperbolic tangent of .5------------------------.462117157

TRUNC

Purpose

Returns n truncated to m decimal places; if m is omitted, to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.

Examples

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL; Truncate----------15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL; Truncate----------10

Purpose	Returns the absolute value of n.
Example	`SELECT ABS(-15) "Absolute" FROM DUAL;` `Absolute----------15`

Purpose	Returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to and are expressed in radians.
Example	`SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;` `Arc_Cosine----------1.26610367`

Purpose	Returns the arc sine of n. Inputs are in the range of -1 to 1, and outputs are in the range of -/2 to /2 and are expressed in radians.
Example	`SELECT ASIN(.3) "Arc_Sine" FROM DUAL;` `Arc_Sine----------.304692654`

Purpose	Returns the arc tangent of n. Inputs are in an unbounded range, and outputs are in the range of -/2 to /2 and are expressed in radians.
Example	`SELECT ATAN(.3) "Arc_Tangent" FROM DUAL;` `Arc_Tangent----------.291456794`

Purpose	Returns the arc tangent of n and m. Inputs are in an unbounded range, and outputs are in the range of - to , depending on the signs of n and m, and are expressed in radians. Atan2(n,m) is the same as atan2(n/m)
Example	`SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL;Arc_Tangent2------------.982793723`

Purpose	Returns smallest integer greater than or equal to n.
Example	`SELECT CEIL(15.7) "Ceiling" FROM DUAL;` `Ceiling----------16`

Purpose	Returns the cosine of n (an angle expressed in radians).
Example	`SELECT COS(180 * 3.14159265359/180)"Cosine of 180 degrees" FROM DUAL;` `Cosine of 180 degrees----------------------1`

Purpose	Returns the hyperbolic cosine of n.
Example	`SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL;Hyperbolic cosine of 0----------------------1`

Purpose	Returns e raised to the nth power; e = 2.71828183 ...
Example	`SELECT EXP(4) "e to the 4th power" FROM DUAL;` `e to the 4th power------------------54.59815`

Purpose	Returns largest integer equal to or less than n.
Example	`SELECT FLOOR(15.7) "Floor" FROM DUAL;` `Floor----------15`

Purpose	Returns the natural logarithm of n, where n is greater than 0.
Example	`SELECT LN(95) "Natural log of 95" FROM DUAL;` `Natural log of 95-----------------4.55387689`

Purpose	Returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number.
Example	`SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL;` `Log base 10 of 100------------------2`

Syntax	MOD(m,n)
Purpose	Returns remainder of m divided by n. Returns m if n is 0.
Example	`SELECT MOD(11,4) "Modulus" FROM DUAL;` `Modulus----------3`
	This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula: `m - n * FLOOR(m/n)`
	The following statement illustrates the difference between the MOD function and the classical modulus: `SELECT m, n, MOD(m, n),m - n * FLOOR(m/n) "Classical Modulus"FROM test_mod_table;` `M N MOD(M,N) Classical Modulus---------- ---------- ---------- -----------------11 4 3 311 -4 3 -1-11 4 -3 1-11 -4 -3 -3`

Purpose	Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.
Example	`SELECT POWER(3,2) "Raised" FROM DUAL;` `Raised----------9`

Syntax	`ROUND(n[,m])`
Purpose	Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.
Example 1	`SELECT ROUND(15.193,1) "Round" FROM DUAL;` `Round----------15.2`
Example 2	`SELECT ROUND(15.193,-1) "Round" FROM DUAL;` `Round----------20`

Syntax	`SIGN(n)`
Purpose	If n<0, the function returns -1; if n=0, the function returns 0; if n>0, the function returns 1.
Example	`SELECT SIGN(-15) "Sign" FROM DUAL;` `Sign-----------1`

Purpose	Returns the sine of n (an angle expressed in radians).
Example	`SELECT SIN(30 * 3.14159265359/180)"Sine of 30 degrees" FROM DUAL;` `Sine of 30 degrees------------------.5`

Purpose	Returns the hyperbolic sine of n.
Example	`SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL;` `Hyperbolic sine of 1--------------------1.17520119`

Purpose	Returns the tangent of n (an angle expressed in radians).
Example	`SELECT TAN(135 * 3.14159265359/180)"Tangent of 135 degrees" FROM DUAL;` `Tangent of 135 degrees----------------------- 1`

Purpose	Returns the hyperbolic tangent of n.
Example	`SELECT TANH(.5) "Hyperbolic tangent of .5"FROM DUAL;` `Hyperbolic tangent of .5------------------------.462117157`