4.5 Platform-Specific Extensions

     

The following sections provide a full listing and description of each vendor-supported function. The functions are platform-specific. Thus, a MySQL function, for example, is not guaranteed to be supported by any other vendor.

4.5.1 DB2-Supported Extensions

This section provides an alphabetical listing of DB2-supported functions that are specific to DB2 and not from the ANSI standard, with examples and corresponding results.

ABSVAL( number )

Synonym for ABS( number ) . For example:

  VALUES(ABSVAL(-1))  -> 1 

ACOS( number )

Returns the arc cosine of a number ranging from -1 to 1. The result ranges from 0 to and is expressed in radians. For example:

  SELECT ACOS(0)  -> 1.570796 

ASCII( text )

Returns the ASCII code of the first character of text. For example:

  SELECT ASCII('x')  -> 120 

ASIN( number )

Returns the arc sine of number ranging from -1 to 1. The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ASIN(0)  -> 0.000000 

ATAN( number )

Returns the arctangent of any number . The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ATAN(3.1415)  -> 1.262619 

ATAN2( number , nbr )

Returns the arctangent of number and nbr . The values for number and nbr are not restricted, but the results range from - to and are expressed in radians. ATAN2( x , y ) is similar to ATAN( y / x ) with the exception that the signs of x and y are used to determine the quadrant of the result. For example:

  VALUES(ATAN2(3.1415, 1))  -> +3.08177595443792E-001 

ATANH( number )

Returns the hyperbolic arctangent of number . The values for number are not restricted, but the results range from - to and are expressed in radians. For example:

  VALUES(ATANH(0))  -> +0.00000000000000E+000 

BIGINT( expression )

Returns a 64-bit integer representation of expression , where expression can be a number, character string, date, time, or timestamp. For example:

  SELECT BIGINT('1991-11-22')  -> 19911122 

BLOB( string [, length ] )

Returns a BLOB representation of string , where string can be a string representation of any type. This example will return BLOB s from the employee_images table that contain the binary string "JFIF."

  SELECT image FROM employee_images WHERE image LIKE BLOB('%JFIF%')  

CHAR( expression [, length ] )

Returns a fixed-length character string representation of expression , where the optional parameter length is the length attribute of the character value returned.

CHR( number )

Returns the character having the ASCII code value equal to number . For example:

  VALUES(CHR(120))  -> 'x' 

CLOB( string [, length ] )

Returns a CLOB representation of string .

COALESCE( expression [,...] )

Returns the first argument that is not NULL. For example:

  SELECT COALESCE(1,2,3)  -> 1 

CONCAT( string1 , string2 )

Returns string1 concatenated with string2 . It is equivalent to the concatenation operator ( ). For example:

  SELECT CONCAT(au_lname, au_fname) FROM authors  -> 'JeffersonThomas' 

COS( number )

Returns the cosine of number as an angle expressed in radians. For example:

  SELECT COS(0)  -> 1.000000 

COSH( number )

Returns the hyperbolic cosine of number . For example:

  VALUES(COSH(3.1415))  -> +1.15908832931176E+001 

COT( number )

Returns the cotangent of number . For example:

  SELECT COT(3.1415)  -> -10792.88993953 

DATE( expression )

Returns a date value from expression . If expression is an integer value, then the returned date is equivalent to expression-1 days after January 1, 0001. For example:

  SELECT DATE(3)  -> '01/03/0001' 

DAY( expression )

Returns the day number in a date expression. For example:

  SELECT DAY('1999-04-15')  -> 15 

DAYNAME( expression )

Returns the name of the day in expression using the locale of the database server. For example:

  SELECT DAYNAME('1999-04-15')  -> 'Thursday' 

DAYOFWEEK( expression ), DAYOFWEEK_ISO( expression ), DAYOFYEAR( expression )

Returns the day of the week or year in expression. DAYOFWEEK returns an integer value in the range 1-7, where 1 represents Sunday. DAYOFWEEK_ISO has the same range as DAYOFWEEK , but the value of 1 represents Monday. DAYOFYEAR returns an integer value in the range 1-366, where the value returned is the day of the year starting with 1 for January 1st. For example:

  VALUES(DAYOFWEEK('1999-04-15'))  -> 5  VALUES(DAYOFWEEK_ISO('1999-04-15'))  -> 4  VALUES(DAYOFYEAR('1999-04-15'))  -> 105   

DAYS( expression )

Returns one more than the number of days between expression and January 1, 0001. For example:

  VALUES(DAYS('1999-04-15'))  -> 729859 

DBCLOB( expression [, length ] )

Returns a DBCLOB representation of a graphic string type, where length specifies the length of the DBCLOB value returned.

DBPARTITIONNUM( column )

Returns the database partition number of the row containing column . For more information, please look to the DB2 user documentation for DBPARTITIONNUM .

DECIMAL( expression [, precision [, scale [, decimal_char ] ), DEC( expression [, precision [, scale [, decimal_char ] )

Returns a decimal representation of expression . The optional arguments control the precision and scale used in the result, whereas decimal_char is the decimal character found in expression , if any.

DECRYPT_BIN( data [, password ] ) or DECRYPT_CHAR( data [, password ] )

Returns decrypted data using the optional password . The result of DECRYPT_BIN is a VARCHAR FOR BIT DATA , whereas DECRYPT_CHAR returns a VARCHAR .

DEGREES( number )

Returns the number of degrees converted from the argument expressed in radians. For example:

  VALUES(DEGREES(3.1415926))  -> +1.79999996929531E+002 

DEREF( expression )

Returns the object reference of expression , where expression must return a REF to an object. For more information on User Defined Types (UDTs) please consult the DB2 User Guide.

DIFFERENCE( expression1 , expression2 )

Returns the difference between the sounds of two strings based on their SOUNDEX values. The result is an integer in the range of 0-4, where 4 is the best SOUNDEX match. For example:

  VALUES(DIFFERENCE('thimble', 'nimble'))  -> 4 

DIGITS( number )

Returns the number argument into a character string of digits. The result of DIGITS does not contain decimal or sign characters and it may be padded on the left with zeros. For example:

  VALUES(DIGITS(DEC('-3.1415926', 12, 8)))  -> 000031415926 

DOUBLE( number )

Returns number converted to a DOUBLE .

ENCRYPT( data [, password [, hint ]] )

Returns data encrypted with the optional password . The optional hint argument allows a user to store up to a 32-byte password hint that is encoded into the result value. To get the password hint, look to the GETHINT function. For example:

  UPDATE employee SET ssn = ENCRYPT(ssn, 'luvbug', 'Herbie is-a?')   WHERE empid = '54321-AD'  

EVENT_MON_STATE( expression )

Returns the state of an event monitor. For details on usage or available event monitors , please look to the DB2 user documentation.

FLOAT( number )

FLOAT is a synonym for DOUBLE .

GETHINT( data )

Returns the password hint encoded in data . The data argument must be encrypted with the ENCRYPT function. For example:

  SELECT GETHINT(ssn) FROM EMPLOYEE WHERE empid = '54321-AD'  'Herbie is-a?' 

GENERATE_UNIQUE()

Returns a 13-byte CHAR(13) FOR BIT DATA value that is guaranteed to be unique from other invocations within the same database. For example:

  INSERT INTO employee(id, emp_name)   VALUES(GENERATE_UNIQUE(), 'Bob Smith')  

GRAPHIC( expression [, length ] )

Returns a fixed-length graphic string representation of expression , where the optional length argument is the length of the result.

HASHEDVALUE( column )

Returns the partitioning map index of the row containing column . For more information, please look to the DB2 user documentation for HASHEDVALUE ().

HEX( expression )

Returns a hexadecimal representation of expression . For example:

  VALUES(HEX(255))  -> 000000FF 

HOUR ( expression )

Returns the hour of part of the time value in expression . For example:

  SELECT HOUR(execution_time), COUNT(*) FROM trades   GROUP BY HOUR(execution_time)  1           2           ----------- -----------           8        2058           9         856          10         912          11         714 . . . 

IDENTITY_VAL_LOCAL()

Returns the value most recently assigned to an identity column. Returns NULL when an identity column has not been inserted into a table since the last COMMIT or ROLLBACK statement.

INSERT( expression1 , expression2 , expression3 , expression4 )

Returns expression1 with expression4 , replacing expression3 bytes at position expression2 . For example:

  VALUES (INSERT('food', 2, 2, 'are'))  -> 'fared' 

INTEGER( expression )

Returns an integer representation of expression .

JULIAN_DAY( expression )

Returns the Julian day number for the date value in expression . The value returned is the number of days between the date value in expression and the start of the Julian date calendar, January 1, 4713 B.C. For example:

  VALUES(JULIAN_DAY('1999-04-15'))  -> 2451284 

LEFT( string , length )

Returns the first length bytes from string . For example:

  VALUES(LEFT('Hello, World!', 5))  -> 'Hello' 

LENGTH( expression )

Returns the integer length of expression , or NULL if expression is NULL. For example:

  VALUES(LENGTH('Hello, World!'))  -> 13 

LOCATE( substring , string [, starting_pos ] )

Returns the location of substring within string or zero, if the substring is not found within string . The optional starting_pos can be used to specify the starting position within string to begin the search. For example:

  VALUES(LOCATE('World', 'Hello, World!'))  -> 8 

LOG( number )

Returns the natural logarithm of number , the same as LN( number ) .

LOG10( number )

Returns the base 10 logarithm of number . For example:

  VALUES(LOG10(50))  -> +1.69897000433602E+000 

LONG_VARCHAR( string )

Returns a LONG_VARCHAR representation of string .

LONG_VARGRAPHIC( string )

Returns a LONG_VARGRAPHIC representation of string .

LTRIM( string )

Removes all characters inset from the left of string . For example:

  VALUES(LTRIM('   Howdy!   '))  -> 'Howdy!   ' 

MICROSECOND( expression )

Returns the microsecond part of the time value in expression . For example:

  VALUES(MICROSECOND(CURRENT_TIMESTAMP))  -> 252270 

MIDNIGHT_SECONDS( expression )

Returns the number of seconds that have elapsed between midnight and the time value in expression . For example:

  VALUES(MIDNIGHT_SECONDS('08:20:15'))  -> 30015 

MINUTE( expression )

Returns the minute part of the time value in expression . For example:

  VALUES(MINUTE('08:20:15'))  -> 20 

MONTH( expression )

Returns the month part of the date value in expression . For example:

  VALUES(MONTH('1999-04-15'))  -> 4 

MONTHNAME( expression )

Returns the month's name for the date value stored in expression using the current locale of the database. For example:

  VALUES(MONTHNAME('1999-04-15'))  -> 'April' 

MULTIPLY_ALT( number1 , number2 )

Returns the product of number1 and number2 . The function MULTIPLY_ALT is a good alternative to the standard multiplication operator ( * ) when the numbers being multiplied have a precision greater than 31. For example:

  VALUES(MULTIPLY_ALT(DECIMAL('256'), DECIMAL('256')))  -> 65536 

NULLIF( expression1 , expression2 )

Returns NULL if expression1 and expression2 are equal; otherwise the function returns expression1 . For example:

  VALUES(NULLIF('1999-04-15', '2000-04-15'))  -> '1999-04-15' 

POSSTR( source , search )

Returns the character position of the first occurrence of search within source where the first character position is 1. For example:

  VALUES(POSSTR('Hello, World!', 'World'))  -> 8 

QUARTER( expression )

Returns an integer ranging from 1 to 4, representing the quarter of the year that contains the date in expression . For example:

  VALUES(QUARTER('2004-04-15'))  -> 2 

RADIANS( expression )

Returns the number of radians from expression , which is expressed in degrees. For example:

  VALUES(RADIANS(180))  -> +3.14159265358979E+000 

RAISE_ERROR( sqlstate , errorstring )

Raises an error upon execution. This function is useful for generating critical errors in complicated SQL statements and stored procedures. The sqlstate parameter is used for passing error code information back to the application executing the SQL statement, and the errorstring parameter is used for a custom error message. For example:

 SELECT a.au_fname, a.aulname,    CASE WHEN t.ytd_sales = 0 THEN 'no sales'         WHEN t.ytd_sales > 0 THEN 'OK'         ELSE RAISE_ERROR('70001', 'Sales should not be negative.')    END FROM authors a, titleauthor, titles t WHERE titleauthor.au_id = a.au_id AND       Titleauthor.title_id = t.title_id GROUP BY a.au_lname, a.au_fname 

RAND( [ seed ] )

Returns a random floating-point value between 0 and 1, using seed as the random number generator seed.

REAL( number )

Returns a single-precision floating point representation of number .

REC2XML( decimal , format , rowtags , column_name [, . . .] )

Returns an XML string containing XML tags, as well as column names and column data. The arguments are described in the following list:

decimal

Decimal value greater than 0, but less than or equal to 6.0, that contains the factor to expand string values to compensate for the string value expanding due to character replacements for the XML values and elements.

format

Case-sensitive string value equal to "COLATTVAL" or "COLATTVAL_XML". If column values can contain XML data that would need to be translated to produce valid XML output, then use "COLATTVAL". If the columns will not contain special XML markup characters, then use "COLATTVAL_XML". Column names containing special markup characters will still be translated (i.e., "escaped") when "COLATTVAL_XML" is chosen .

rowtags

String value to name the row element containing the column data. If rowtags is an empty string, then the value of row is used. If rowtags contains a string of only spaces, then the enclosing row element will be omitted in the output.

column_name

The name of a column to place into the XML result. For example:

 SELECT REC2XML(1.3, 'COLATTVAL', 'Author', au_id, au_fname,     au_lname)  FROM AUTHORS WHERE au_id = '172-32-1176' <Author>    <column name="AU_ID">172-32-1176</column>    <column name="AU_FNAME">Johnson</column>    <column name="AU_LNAME">White</column> </Author> 

REPEAT( string , number )

Returns a character string that is composed of string repeated number times. For example:

  VALUES(CHAR(REPEAT('Duck ', 3)))  -> 'Duck Duck Duck' 

REPLACE( string , search_string , replacement_string )

Returns string with every occurrence of search_string replaced with replacement_string . For example:

  VALUES(REPLACE('change', 'e', 'ing'))  -> 'changing' 

RIGHT( string , number )

Returns the number rightmost bytes from string . For example:

  VALUES(RIGHT('Hello, World!', 6))  -> 'World!' 

ROUND( number[ , decimal] )

Returns number rounded to decimal places right of the decimal point. When decimal is omitted, number is rounded to places. Note that decimal , an integer, can be negative to round off digits left of the decimal point. For example:

  VALUES(ROUND(12345.6789, 2))  -> 12345.6800 

RTRIM( string )

Returns string with all trailing whitespace characters removed. For example:

  VALUES(RTRIM('   welcome    '))  -> '   welcome' 

SECOND( expression )

Returns the seconds part of the time value in expression . For example:

  VALUES(SECOND('08:20:15'))  -> 15 

SIGN( number )

When number < 0, returns -1. When number = 0, returns 0. When number > 0, returns 1. For example:

  VALUES(SIGN(-3.1415926), SIGN(0), SIGN(3.1415926))  -1.00000000000000E+000           0   +1.00000000000000E+000 

SIN( number )

Returns the sine of number , where number is in radians. For example:

  SELECT SIN(0)  -> 0.000000 

SINH( number )

Returns the hyperbolic sine of number .

SMALLINT( number )

Returns a SMALLINT value equivalent to number .

SOUNDEX( string )

Returns a character string containing the phonetic representation of string . This function allows words that are spelled differently but sound alike in English to be compared for equality. For example:

  VALUES(SOUNDEX('thimble'))  -> 'T514' 

SPACE( number )

Returns a string composed of number spaces. For example:

  VALUES(SPACE(5))  -> '     ' 

STDDEV( { ALL DISTINCT } expression )

Returns the standard deviation of the values contained within expression . The ALL keyword specifies the default behavior in which all values in expression are used in the standard deviation. The keyword DISTINCT omits duplicate values in expression when doing the calculation. For example:

  SELECT STDDEV(values) FROM NUMBERS  -> 0.0642 

SUBSTR( string , start [, length ] )

Returns a substring of string of length bytes starting at start . If length is too large to permit a valid substring, then string is padded with trailing spaces until a valid substring can be found. The starting position, start , must be an integer value in the range of 1 to the length of string . For example:

  VALUES(SUBSTR('Hello, World!', 8, 5))  -> 'World' 

TABLE_NAME( object [, schema ] )

Returns the unqualified name of object after resolving any aliases. The optional schema argument specifies the database schema to use in resolving the object name. For example:

  VALUES(TABLE_NAME('trades'))  -> 'trades' 

TABLE_SCHEMA( object [, schema ] )

Returns the schema name of the object after resolving any aliases. The optional schema argument specifies the database schema to use in resolving the object name. For example:

  VALUES(TABLE_SCHEMA('trades'))  -> 'MYSCHEMA' 

TAN( number )

Returns the tangent of number , where number is in radians. For example:

  SELECT TAN(3.1415)  -> -0.000093 

TANH( number )

Returns the hyperbolic tangent of number .

TIME( expression )

Returns a time value equivalent to the time in expression . For example:

  VALUES(TIME('2003-04-15 08:20:15'))  -> '08:20:15' 

TIMESTAMP( expression1 [, expression2 ] )

Returns a timestamp value created from date and time values in expression1 and expression2 . If only expression1 is given, then it must contain a valid date and time value. If both arguments are given, then expression1 must contain a valid string representation of a date and expression2 a valid string representation of time. For example:

  VALUES(TIMESTAMP('2003-04-15', '08:20:15'))  2003-04-15-08.20.15.000000 

TIMESTAMP_FORMAT( string , format )

Returns a timestamp value after extracting it from string . The second argument, format , contains the timestamp format used in string . (For details on the timestamp format, see the VARCHAR_FORMAT function.) For example:

  VALUES(TIMESTAMP_FORMAT('2003-4-15 08:20:15',      'YYYY-MM-DD HH24:MI:SS'))  2003-04-15-08.20.15.000000 

TIMESTAMP_ISO( expression )

Returns a timestamp value from expression . If expression is a date value, then the time value will consist of zeros. If the expression is a time value, then the current date will be used in constructing the timestamp value. For example:

  VALUES(TIMESTAMP_ISO('2003-04-15'))  -> 2003-04-15-00.00.00.000000 

TIMESTAMPDIFF( interval_type , tsdiff_expression )

Returns an estimated number of intervals between two timestamp values. The interval_type argument must be a power of 2 in the range between 1 and 256, where the meaning of the value is interpreted as in the following table:

interval_type	Timestamp difference returned
1	Fractions of a second
2	Seconds
4	Minutes
8	Hours
16	Days
32	Weeks
64	Months
128	Quarters
256	Years

The tsdiff_expression must be the result of subtracting two timestamp values and converting to a single CHAR(22) value. For example:

  VALUES(TIMESTAMPDIFF(2, CHAR(CURRENT_DATE - CURRENT_TIMESTAMP)))  515 

TO_CHAR( timestamp , format )

Synonym for VARCHAR_FORMAT .

TO_DATE( string , format )

Synonym for TIMESTAMP_FORMAT .

TRANSLATE( string , tostring , fromstring [, pad_char ] )

Returns a string equivalent to the string argument with each character found in tostring replaced with the corresponding character from fromstring . The optional pad_char argument can contain a single-byte character that will be used to pad tostring if it is shorter than fromstring . If the same character appears more than once in fromstring , then only the first occurrence will count and all following occurrences will be ignored. For example:

  VALUES(TRANSLATE('123,456,789.45', ',.', '.,'))  -> '123.456.789,45' 

TRUNCATE( number1 , number2 ) or TRUNC( number1 , number2 )

Returns number1 with all digits to the right of the decimal point by number2 places replaced with zeros. If number2 is negative, then the truncation occurs to the left of the decimal place. For example:

  VALUES(TRUNCATE(DEC('123.456'), -1))  -> 120. 

TYPE_ID( expression )

Returns the internal type identifier to the user-defined structured type instance stored in expression . For more information on User Defined Types (UDTs) please consult the DB2 User Guide.

TYPE_NAME( expression )

Returns the unqualified name to the user-defined structured type instance stored in expression . For more information on User Defined Types (UDTs) please consult the DB2 User Guide.

TYPE_SCHEMA( expression )

Returns the schema name to the user-defined structured type instance stored in expression . For more information on User Defined Types (UDTs) please consult the DB2 User Guide.

VALUE( expression [,...] )

Returns the first argument that is not NULL. For example:

  VALUES(VALUE('Hello!', 5))  -> 'Hello!' 

VAR or VARIANCE( { ALL DISTINCT } expression )

Returns the variance of the values contained within expression . The ALL keyword specifies the default behavior in which all values in expression are used in the calculation. The keyword DISTINCT omits duplicate values in expression when doing the calculation. For example:

  SELECT VARIANCE(values) FROM NUMBERS  -> 987244882.22 

VARCHAR( expression [, length ] )

Returns a varying-length character representation of expression , where length is the length attribute for the varying-length character result. If length is omitted, then the length of the result is the length of the expression.

VARCHAR_FORMAT( expression , format )

Returns a string representation of the timestamp value in expression in the format provided in the format argument. Listed below are the available specifiers for format and their meanings.

YYYY

Four-digit year

MM

Two-digit month (01-12)

DD

Two-digit day of month (01, 02, ...)

HH24

Two-digit hour of day (00-24)

MI

Two-digit minute (00-59)

SS

Two-digit second (00-59)

For example:

  VALUES(VARCHAR_FORMAT(CURRENT_TIMESTAMP,       'YYYY-MM-DD HH24:MI:SS'))  2003-06-24 09:37:45 

VARGRAPHIC( expression [, length ] )

Returns a varying-length graphic string representation of expression , where length is the length attribute for the varying-length graphic string result. If length is omitted, then the length of the result is the length of the expression.

WEEK( expression )

Returns the week of the year for the date value contained in expression . The range for the result is 1-54 and the week starts with Sunday. For example:

  VALUES(WEEK('2003-04-15'))  -> 16 

WEEK_ISO( expression )

Returns the week of the year for the date value contained in expression . The range for the result is 1-53, and the week starts with Monday. The first week of the year always contains January 4 ^th . For example:

  VALUES(WEEK_ISO('2003-04-15'))  -> 16 

YEAR( expression )

Returns the year part of the date value in expression . For example:

  VALUES(YEAR('2004-04-15'))  -> 2004 

4.5.2 MySQL-Supported Functions

This section provides an alphabetical listing of MySQL-supported functions, with examples and corresponding results.

ACOS( number )

Returns the arc cosine of number ranging from -1 to 1. The result ranges from 0 to and is expressed in radians. For example:

  SELECT ACOS(0)  -> 1.570796 

ASCII( text )

Returns the ASCII code of the first character of text . For example:

  SELECT ASCII('x')  -> 120 

ASIN( number )

Returns the arc sine of number ranging from -1 to 1. The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ASIN(0)  -> 0.000000 

ATAN( number )

Returns the arctangent of any number . The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ATAN(3.1415)  -> 1.262619 

ATAN2( x,y )

Returns the arctangent of the two variables x and y . ATAN2( x , y ) is similar to ATAN( y / x ) , with the exception that the signs of x and y are used to determine the quadrant of the result. For example:

  ATAN2(3.1415, 1)  -> 1.262619 

BENCHMARK( count , expr )

Executes the expression (expr) count times. The result value is always 0. For example:

  BENCHMARK(1000000,ATAN2(3.1415, 1))  -> 0 

BIN( number )

Returns a string containing the binary value of n , where n is a BIGINT number.

BINARY string

Casts string to a binary string.

BIT_AND( expr )

An aggregate function that returns the bitwise AND of all bits in expr . The calculation is performed with 64-bit ( BIGINT ) precision. The value of -1 is returned when no matching rows are found. For example:

  BIT_AND(mycolumn)  -> 0 

BIT_COUNT( number )

Returns the number of bits that are set in number . For example:

  BIT_COUNT(5)  -> 2 

BIT_OR( expr )

An aggregate function that returns the bitwise OR of all bits in expr . The calculation is performed with 64-bit ( BIGINT ) precision. The value of zero is returned when no matching rows are found. For example:

  BIT_OR(mycolumn)  -> 1   

CHAR( number [,...] )

Returns a string consisting of the characters given by the ASCII code values in the arguments. Any NULL values are ignored. For example:

  CHAR(120,121,122)  -> 'xyz' 

COALESCE( list )

Returns the first non-NULL element in the list. For example:

  COALESCE(NULL, 1, 2)  -> 1 

COMPRESS( string )

Returns a compressed version of string .

CONCAT_WS( separator , str1 , str2 [,...] )

A special form of CONCAT() that inserts separator between every pair of string arguments concatenated. If separator is NULL, then the result is NULL. For example:

  CONCAT_WS(', ', au_lname, au_fname)  -> 'Jefferson, Thomas' 

CONNECTION_ID()

Returns the connection ID for the connection. Every connection has its own unique ID. For example:

  CONNECTION_ID()  -> 305102 

CONV( number , from_base , to_base )

Returns a string representation of the number number , converted from base from_base to base to_base . If any argument is NULL, then the result is NULL. For example:

  CONV(12,10,2)  -> 1100 

COS( number )

Returns the cosine of number as an angle expressed in radians. For example:

  SELECT COS(0)  -> 1.000000 

CURDATE()

Returns today's date as a value in YYYY-MM-DD or YYYYMMDD format, depending on whether the function is used in a string or numeric context. For example:

  CURDATE()  -> '2003-06-24' 

CURTIME()

Returns the current time as a value in HH:MM:SS or HHMMSS format, depending on whether the function is used in a string or numeric context. For example:

  CURTIME()  -> '20:40:20' 

DATABASE()

Returns the current database name. For example:

  DATABASE()  -> 'PUBS' 

DATE_ADD( date , INTERVAL expr type )

 

DATE_SUB( date , INTERVAL expr type )

 

ADDDATE( date , INTERVAL expr type )

 

SUBDATE( date , INTERVAL expr type )

These functions perform date arithmetic calculations. ADDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB() . DATE_ADD() returns the result of adding the INTERVAL to the date expression. DATE_SUB() is the result of subtracting the INTERVAL from the date expression. For example:

  DATE_ADD('1999-04-15', INTERVAL 1 DAY)  -> '1999-04-16'  DATE_SUB('1999-04-15', INTERVAL 1 DAY)  -> '1999-04-14' 

DATE_ FORMAT( date , format )

Formats the date value according to the format string. Listed below are the available specifiers for format and their meanings.

%a

Abbreviation of the day (Sun-Sat)

%b

Abbreviation of the month (Jan-Dec)

%c

Month number (1-12)

%D

Day of month with a suffix (1 ^st , 2 ^nd , 3 ^rd , ...)

%d

Two digit day of month (01, 02, ...)

%e

Day of month (1, 2, 3, ...)

%H

Hour (00-23)

%h

Hour (01-12)

%i

Minutes (00-59)

%I

Hour (01-12)

%j

Day of year (001-366)

%k

Hour (0-23)

%l

Hour (1-12)

%M

Full month name (January-December)

%m

Month (01-12)

%p

A.M. or P.M.

%r

12-hour time (hh:mm:ss A.M or P.M)

%S, %s

Seconds (00-59)

%T

24-hour time (hh:mm:ss)

%U

Week number (00-53, Sunday being the first day of the week)

%u

Week number (00-53, Monday being the first day of the week)

%V

Week number (01-53, Sunday being the first day of the week)

%v

Week number (01-53, Monday being the first day of the week)

%W

Name of the day (Sunday-Saturday)

%w

Day of the week (0 = Sunday, 6 = Saturday)

%X

Four-digit year with Sunday being the first day of the week

%x

Four-digit year with Monday being the first day of the week

%Y

Four-digit year

%y

Two-digit year

%%

Literal "%"

For example:

  DATE_FORMAT('1999-04-15', '%M-%D-%Y')  -> 'April-15th-1999' 

DAYNAME( date )

Returns the name of the weekday for date . For example:

  DAYNAME('1999-04-15')  -> 'Thursday' 

DAYOFMONTH( date )

Returns the day of the month for date , in the range 1 to 31. For example:

  DAYOFMONTH('1999-04-15')  -> 15 

DAYOFWEEK( date )

Returns the weekday index for date (1 = Sunday, 2 = Monday, . . . 7 = Saturday). For example:

  DAYOFWEEK('1999-04-15')  -> 5 

DAYOFYEAR( date )

Returns the day of the year for date , in the range 1 to 366. For example:

  DAYOFYEAR('1999-04-15')  -> 105 

DECODE( crypt_str , pass_str )

Decrypts the encrypted string crypt_str using pass_str as the password; crypt_str should be a string returned from ENCODE(). For example:

  DECODE(ENCODE('foo','bar'),'bar')  -> 'foo' 

DEGREES( number )

Returns the argument number converted from radians to degrees. For example:

  DEGREES(3.1415926)  -> 179.99999692953 

ELT( n , str1 , str2 , str3 [,...n] )

Returns str1 if n = 1, str2 if n = 2, and so on. If n is less than 1 or greater than the number of arguments, then this function returns NULL. ELT() is the complement of FIELD() . For example:

  ELT(1, 'Hi', 'There')  -> 'Hi'  ELT(2, 'Hi', 'There')  -> 'There'   

EMPTY_BLOB and EMPTY_CLOB

Returns empty LOB locators, which you can use to initialize BLOB and CLOB values that you wish to create as part of an INSERT or UPDATE statement.

ENCODE( str , pass_str )

Encrypts str using pass_str as the password. To decrypt the result, use DECODE() . The result is a binary string the same length as the string. For example:

  DECODE(ENCODE('foo','bar'),'bar')  -> 'foo' 

ENCRYPT( str [, salt ] )

Encrypts str using the Unix crypt( ) system call. The salt argument should be a string with two characters. For example:

  ENCRYPT('password')  -> 'ZB7yqPUHvNnmo' 

EXPORT_SET( bits , on , off ,[ separator ,[ number_of_bits ]] )

Returns a string where every bit set in bits gets an on string and every unset bit gets an off string. Each string is separated with separator ; the default is a comma (,). Only number_of_bits of bits is used; the default is 64. For example:

  EXPORT_SET(4,'T','F')  F,F,T,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F, F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F,F 

FIELD( str , str1 , str2 , str3 [ ,... ])

Returns the index of str in the given string arguments. Returns 0 if str is not found. FIELD() is the complement of ELT() . For example:

  FIELD('GOOSE','DUCK','DUCK','GOOSE','DUCK')  -> 3 

FIND_IN_SET( str , strlist )

Returns the index of str within the strlist , where strlist is a list of strings separated by commas. This function is equivalent to calling FIELD( str , CONCAT_WS( ' , ' , str1 , str2 , str3 , [ ,... ])) . For example:

  FIND_IN_SET('b','a,b,c,d')  -> 2 

FORMAT( number , decimals )

Formats the number number to a format like #,###,###.##, rounded to decimals decimals. If decimals is 0, the result has no decimal point or fractional part. For example:

  FORMAT(12345.2132,2)  -> 12,345.21  FORMAT(12345.2132,0)  -> 12,345 

FROM_DAYS( number )

Given a day number number , returns a DATE value. This function should not be used for values that precede the advent of the Gregorian calendar (1582), due to the days lost when the calendar was changed. For example:

  FROM_DAYS(888888)  -> 2433-09-10 

FROM_UNIXTIME( unix_timestamp )

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. For example:

  FROM_UNIXTIME(888123892)  -> 1998-02-21 21:04:52 

FROM_UNIXTIME( unix_timestamp , format )

Returns a string representation of the unix_timestamp , formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function. For example:

  FROM_UNIXTIME(888123892,'%Y %D %M')  -> '1998 21st February' 

GET_LOCK( str , timeout )

Tries to obtain a lock with a name given by the string str , with a time-out of timeout seconds. Returns 1 if the lock is obtained successfully or NULL if an error occurs or the attempt to acquire the lock times out. For example:

  GET_LOCK('lochness',10)  -> 1 

GREATEST( x , y [,...] )

Returns the largest argument. For example:

  GREATEST(8,2,4)  -> 8 

HEX( number )

Returns a string representation of the hexadecimal value of number . This is equivalent to CONV ( number,10,16 ). For example:

  HEX(255)  -> FF 

HOUR( time )

Returns the hour for time , in the range 0 to 23. For example:

  HOUR('08:20:15')  -> 8 

IF( expr1 , expr2 , expr3 )

Returns expr2 if expr1 is TRUE, otherwise returns expr3 . For example:

  IF(1,'yes','no')  -> 'yes'  IF(0,'yes','no')  -> 'no'   

IFNULL( expr1 , expr2 )

Returns expr1 if expr1 is not NULL, otherwise returns expr2 . For example:

  IFNULL(0,'NULL')  -> 0  IFNULL(NULL,'NULL')  -> 'NULL' 

INSERT( str , pos , len , newstr )

Returns the string str with newstr inserted at character position pos for length len . For example:

  INSERT('paper',2,3,'ea')  -> 'pear' 

INSTR( str , substr )

Returns the position of the first occurrence of the substring substr in the string str . For example:

  INSTR('ducks','c')  -> 3 

INTERVAL( num1 , num2 , num3 , num4 [,...n] )

Returns 0 if num1 < num2 , 1 if num1 < num3 , and so on. It is required that num2 < num3 < num4 < . . . < numN . For example:

  INTERVAL(5,1,6)  -> 1  INTERVAL(5,2,3,7,9)  -> 2   

IS_FREE_LOCK( lock )

Returns 1 if lock is free and zero if the lock is currently in use. The function may return NULL on error conditions. For example:

  IS_FREE_LOCK('lochness')  -> 0 

ISNULL( expr )

If expr is NULL, IFNULL () returns 1; otherwise it returns 0. For example:

  ISNULL(1)  -> 0  ISNULL(NULL)  -> 1   

LAST_INSERT_ID( [ expr ] )

Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. For example:

  LAST_INSERT_ID()  -> 0 

LCASE( str )

Synonym for lower ( str ). For example:

  LCASE('DUCK')  -> 'duck' 

LEAST( X , Y [,...n] )

With two or more arguments, returns the smallest (minimum-valued) argument. For example:

  LEAST(10,5,3,7)  -> 3 

LEFT( str , len )

Returns the leftmost len characters from the string str . For example:

  LEFT('Ducks', 4)  -> 'Duck' 

LENGTH( str )

These functions return the length of the string str . For example:

  LENGTH('DUCK')  -> 4 

LOAD_FILE( file_name )

Reads the file and returns the file contents as a string. The file must be on the server, and the user must specify the full pathname to the file and have access to the file.

LOCATE( substr , str) , POSITION( substr IN str )

Returns the position of the first occurrence of the substring substr in the string str . Returns zero if substr is not in str . LOCATE is a synonym for the standard POSITION( substr IN str ) . For example:

  LOCATE('al','Donald')  -> 4  POSITION('al' IN 'Donald')  -> 4 

LOCATE( substr , str , pos )

Returns the position of the first occurrence of the substring substr in the string str , starting at position pos ; returns zero if substr is not in str . For example:

  LOCATE('World', 'Hello, World!')  -> 8 

LOG(X)

Returns the natural logarithm of X . For example:

  LOG(50)  -> 3.912023 

LOG2(X)

Returns the base-2 logarithm of X . For example:

  LOG2(50)  -> 5.64386 

LOG10( X )

Returns the base-10 logarithm of X . For example:

  LOG10(50)  -> 1.698970 

LPAD( str , len , padstr )

Returns the string str , left-padded with the string padstr until str is len characters long. For example:

  LPAD('ucks',6,'d')  -> 'dducks' 

LTRIM( str )

Returns the string str with leading-space characters removed. For example:

  LTRIM('   Howdy!   ')  -> 'Howdy!   ' 

MAKE_SET( bits , str1 , str2 [, ...n] )

Returns a set (a string containing substrings separated by commas) consisting of the string arguments that have the corresponding bit in bits set; str1 corresponds to bit 0, str2 to bit 1, etc. NULL strings in str1 , str2 , ... are not appended to the result. For example:

  MAKE_SET(1  4,'hello','nice','world')  -> 'hello,world' 

MD5( string )

Calculates an MD5 checksum for the string . Value is returned as a 32-digit-long hex number. For example:

  MD5('somestring')  -> 1f129c42de5e4f043cbd88ff6360486f 

MINUTE( time )

Returns the minute for time , in the range to 59. For example:

  MINUTE('08:20:15')  -> 20 

MONTH( date )

Returns the month for date , in the range 1 to 12. For example:

  MONTH('1999-04-15')  -> 4 

MONTHNAME( date )

Returns the name of the month for date . For example:

  MONTHNAME('1999-04-15')  -> 'April' 

NOW() , SYSDATE()

Returns the current date and time as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. For example:

  NOW()  -> 2003-06-24 20:40:24  SYSDATE()  -> 2003-06-24 20:40:24  CURRENT_TIMESTAMP  -> 2003-06-24 20:40:24 

NULLIF( expr1 , expr2 )

Returns NULL if expr1 is equal to expr2 ; otherwise returns expr1 . For example:

  NULLIF(2,29)  -> 2  NULLIF(29,29)  -> NULL   

OCT( n )

Returns an octal value equivalent of n , where n is a number. This is equivalent to CONV ( N , 10 , 8 ). Returns NULL if n is NULL. For example:

  OCT(255)  -> 377 

ORD( str )

Returns the character ordinal of the multibyte character string str . The value is calculated using the following formula: ((first byte ASCII code)*256+(second byte ASCII code)*256*256)(third byte ASCII code)*256*256*256[,...]. If str isn't a multibyte character, then this function returns the same value as the ASCII() function. For example:

  ORD('29')  -> 50 

PASSWORD( str )

Calculates a password string from the plain-text password str . This is the function that is used for encrypting MySQL passwords. For example:

  PASSWORD('password')  -> 5d2e19393cc5ef67 

PERIOD_ADD( period , months )

Adds the number of months found in months to the period in period (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. For example:

  PERIOD_ADD(9902,3)  -> 199905 

PERIOD_DIFF( period1 , period2 )

Returns the number of months between period1 and period2 . period1 and period2 should be in the format YYMM or YYYYMM. For example:

  PERIOD_DIFF(9902,9905)  -> -3 

PI()

Returns the value of -. For example:

  PI()  -> 3.141593 

POW( X , Y ), POWER( X , Y )

Returns the value of X raised to the power of Y . For example:

  POW(2, 8)  -> 256.000000 

QUARTER( date )

Returns the quarter of the year for date , in the range 1 to 4. For example:

  QUARTER('1999-04-15')  -> 2 

RADIANS( X )

Returns the argument X , converted from degrees to radians. For example:

  RADIANS(180)  -> 3.1415926535898 

RAND() , RAND( N )

Returns a random floating-point value in the range 0 to 1.0. If an integer argument N is specified, it is used as the seed value. For example:

  RAND()  -> 0.29588872501244 

RELEASE_LOCK( str )

Releases the lock named by the string str that was obtained with GET_LOCK() . Returns 1 if the lock is released or NULL if the named lock doesn't exist or isn't locked by this thread (in which case the lock is not released). For example:

  RELEASE_LOCK('lochness')  -> 1 

REPEAT( str , count )

Returns a string consisting of the string str repeated count times. For example:

  REPEAT('Duck', 3)  -> 'DuckDuckDuck' 

REPLACE( str , from_str , to_str )

Returns the string str with all occurrences of the string from_str replaced by the string to_str . For example:

  REPLACE('change', 'e', 'ing')  -> 'changing' 

REVERSE( str )

Returns the string str reversed . For example:

  REVERSE('STOP')  -> 'POTS' 

RIGHT( str , ten )

Returns the rightmost 10 characters from the string str . For example:

  RIGHT('Hello, World!', 6)  -> 'World!' 

ROUND( X[ , D] )

Returns the argument X , rounded to a number with D decimals. If D is 0, the result has no decimal point or fractional part. For example:

  ROUND(12345.6789, 2)  -> 12345.68 

RPAD( str , len , padstr )

Returns the string str , right-padded with the string padstr until str is len characters long. For example:

  RPAD('duck',6,'s')  -> 'duckss' 

RTRIM( str )

Returns the string str with trailing space characters removed. For example:

  RTRIM('   welcome    ')  -> 'welcome    ' 

SEC_TO_TIME( seconds )

Returns the seconds argument, converted to hours, minutes, and seconds, as a value in HH:MM:SS or HHMMSS format, depending on whether the function is used in a string or numeric context. For example:

  SEC_TO_TIME(256)  -> 00:04:16 

SECOND( time )

Returns the second for time , in the range to 59. For example:

  SECOND('08:20:15')  -> 15 

SHA( X ) or SHA1( X )

Returns a SHA1 160-bit checksum for X . For example:

  SHA('abc')  -> 'a9993e364706816aba3e25717850c26c9cd0d89d' 

SIGN( X )

Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive. For example:

  SIGN(-3.1415926)  -> -1 

SIN( number )

Returns the sine of number , where number is in radians. For example:

  SELECT SIN(0)  -> 0.000000 

SOUNDEX( str )

Returns a soundex string from str . For example:

  SOUNDEX('thimble')  -> 'T514' 

expr1 SOUNDS LIKE expr2

Synonymous with the expression:

 SOUNDEX(   expr1   ) = SOUNDEX(   expr2   ) 

SPACE( n )

Returns a string consisting of n space characters. For example:

  SPACE(5)  -> '     ' 

STD( expr ) , STDDEV( expr )

Returns the standard deviation of expr . The STDDEV() form of this function is provided for Oracle compatibility. For example:

  STD(5)  -> NULL 

STRCMP( expr1 , expr2 )

STRCMP() returns zero, as marked if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise. For example:

  STRCMP('DUCKY', 'DUCK')  -> 1  STRCMP('DUCK', 'DUCK')  -> 0   

SUBSTRING( str , pos , len ) , MID( str , pos , len )

Returns a substring len characters long from string str , starting at position pos . These functions are synonyms for the ANSI SQL92 function SUBSTRING( str FROM pos FOR len ) . For example:

  SUBSTRING('Hello, World!', 8, 10)  -> 'World!'  SUBSTRING('Hello, World!' FROM 8 FOR 10)  -> 'World!'   

SUBSTRING( str , pos ), SUBSTRING( str FROM pos )

Returns a substring from string str starting at position pos . For example:

  SUBSTRING('Hello, World!', 8)  -> 'World!'  SUBSTRING('Hello, World!' FROM 8)  -> 'World!' 

SUBSTRING_INDEX( str , delim , count )

Returns the substring of str after count occurrences of the delimiter delim . For example:

  SUBSTRING_INDEX('www.mysql.com', '.', 2)  -> 'www.mysql' 

TAN( number )

Returns the tangent of number , where number is in radians. For example:

  SELECT TAN(3.1415)  -> -0.000093 

TIME_FORMAT( time , format )

This is used like DATE_FORMAT(), but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0. See DATE_FORMAT for the format specifiers available. For example:

  TIME_FORMAT('2003-04-15 08:20:15', '%r')  -> 08:20:15 AM 

TIME_TO_SEC( time )

Returns the time argument, converted to seconds. For example:

  TIME_TO_SEC('08:20:15')  -> 30015 

TO_DAYS( date )

Given a date , returns a day number (the number of days since the year 0). For example:

  TO_DAYS('1999-04-15')  -> 730224 

TRUNCATE( X , D )

Returns the number X , truncated to D decimals. If D is 0, the result has no decimal point or fractional part. For example:

  TRUNCATE('123.456', 2)  -> '123.45'  TRUNCATE('123.456', 0)  -> '123'  TRUNCATE('123.456', -1)  -> '120' 

UCASE( str )

Synonym for UPPER( str ) . For example:

  UCASE('duck')  -> 'DUCK' 

UNCOMPRESS( string )

Returns a compressed version of string .

UNHEX( str )

Returns a binary string constructed from hex characters in str .

UNIX_TIMESTAMP() , UNIX_TIMESTAMP( date )

If called with no argument, returns a Unix timestamp (seconds since 1970-01-01 00:00:00 GMT). If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since 1970-01-01 00:00:00 GMT. For example:

  UNIX_TIMESTAMP()  -> 1056512427  UNIX_TIMESTAMP('1999-04-15')  -> 924159600 

USER() , SYSTEM_USER() , SESSION_USER()

These functions return the current MySQL username. For example:

  USER()  -> '[e-mail]'  SYSTEM_USER()  -> '[e-mail]'  SESSION_USER()  -> '[e-mail]' 

VERSION()

Returns a string indicating the MySQL server version. For example:

  VERSION()  -> '4.0.12-standard' 

WEEK( date ) , WEEK( date , first )

With a single argument, returns the week for date , in the range 1 to 53. (The beginning of a week 53 is possible during some years.) The two-argument form of WEEK() allows the user to specify whether the week starts on Sunday (0) or Monday (1). For example:

  WEEK('1999-04-15')  -> 15 

WEEKDAY( date )

Returns the weekday index for date (0 = Monday, 1 = Tuesday, . . . 6 = Sunday). For example:

  WEEKDAY('1999-04-15')  -> 3 

YEAR( date )

Returns the year for date , in the range 1000 to 9999. For example:

  YEAR('1999-04-15')  -> 1999 

YEARWEEK( date ) , YEARWEEK( date , first )

Returns the year and week for date . The second argument works exactly like the second argument to WEEK(). Note that the year may be different from the year in the date argument for the first and the last week of the year. For example:

  YEARWEEK('1999-04-15')  -> 199915 

4.5.3 Oracle-Supported Functions

This section provides an alphabetical listing of the SQL functions specific to Oracle, with examples and corresponding results.

ACOS( number )

Returns the arc cosine of number ranging from -1 to 1. The result ranges from 0 to 1 and is expressed in radians. For example:

  SELECT ACOS(0) FROM DUAL  -> 1.570796 

ADD_MONTHS( date , int )

Returns the date plus int months. For example:

  SELECT ADD_MONTHS('15-APR-1999', 3) FROM DUAL  -> 15-JUL-99 

ASCII( text )

Returns the ASCII code of the first character of text . For example:

  SELECT ASCII('x') FROM DUAL  -> 120 

ASCIISTR( text )

Converts text from any character set into an ASCII equivalent. Characters in text that have no equivalent in ASCII will be replaced with the string \XXXX , where XXXX represents the UTF-16 code unit. For example:

  SELECT ASCIISTR('BC') FROM DUAL  -> ' 
  SELECT ASCIISTR(' BC') FROM DUAL  -> '\00C4BC' 
 C4BC' 

ASIN( number )

Returns the arc sine of number ranging from -1 to 1. The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ASIN(0) FROM DUAL  -> 0.000000 

ATAN( number )

Returns the arctangent of number . The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ATAN(3.1415) FROM DUAL  -> 1.262619 

ATAN2( number , nbr )

Returns the arctangent of number and nbr . The values for number and nbr are not restricted, but the results range from - to and are expressed in radians. ATAN2( x , y ) is similar to ATAN( y / x ) , with the exception that the signs of x and y are used to determine the quadrant of the result. For example:

  SELECT ATAN2(3.1415, 1) FROM DUAL  -> 1.26261873 

BFILENAME( directory , filename )

Returns a BFILE locator associated with a physical LOB binary file on the server's filesystem in directory with the name filename .

BIN_TO_NUM( expr[ ,...n ] )

Returns a decimal number equivalent of the binary bit vector contained in the expr arguments. For example:

  SELECT BIN_TO_NUM(1,0,1) FROM DUAL  -> 5 

BITAND( integer1, integer2 )

Returns the bitwise AND of the two integer arguments. For example:

  SELECT BITAND(101, 2) FROM DUAL  -> 0    SELECT BITAND(column1, 1) FROM DUAL  -> 1 

CARDINALITY( nested_table )

Returns the number of elements (cardinality) of the nested_table . If the nested_table is empty, NULL will be returned. For example:

  SELECT CARDINALITY(mytable) FROM DUAL  -> 6 

CHARTOROWID( char )

Converts a value from a character datatype ( CHAR or VARCHAR2 datatype) to a ROWID datatype.

CHR( number [ USING NCHAR_CS ] )

Returns the character having the binary equivalent to number in either the database character set (if USING NCHAR_CS is not included) or the national character set (if USING NCHAR_CS is included).

COALESCE( list )

Returns the first non-NULL element in the list. For example:

  SELECT COALESCE(NULL, 1, 2) FROM DUAL  -> 1 

COLLECT ( column )

Creates for each group a nested table consisting of all values in a column. This is an aggregate function.

COMPOSE( string )

Returns string as a fully normalized UNICODE string.

CONCAT( string1 , string2 )

Returns string1 concatenated with string2 . CONCAT is equivalent to the concatenation operator ( ). For example:

  SELECT CONCAT(au_lname, au_fname) FROM authors  -> 'JeffersonThomas' 

CONVERT( char_value , target_char_set , source_char_set )

Converts a character string from one character set to another; returns char_value in the target_char_set after converting char_value from the source_char_set .

CORR_K( expr1 , expr2 [, return_type ] )

 

CORR_S( expr1 , expr2 [, return_type ] )

CORR_K returns Kendall's tau-b correlation coefficient, and CORR_S returns Spearman's rho correlation coefficient of a set of numbered pairs ( expr1 and expr2 ). The return_type argument, a VARCHAR2 , can be omitted or one of the following values: ' COEFFICIENT ', ' ONE_SIDED_SIG ', ' TWO_SIDED_SIG '. The value of ' COEFFICIENT ', which specifies the default behavior, returns the coefficent of the correlation. The values of ' ONE_SIDED_SIG ' and ' TWO_SIDED_SIG ' will return the one- and two-tailed significance of the correlation, respectively.

COS( number )

Returns the cosine of number as an angle expressed in radians. For example:

  SELECT COS(0) FROM DUAL  -> 1.000000 

COSH( number )

Returns the hyperbolic cosine of number. For example:

  SELECT COSH(180) FROM DUAL  -> 7.4469E+77  SELECT COT(3.1415) FROM DUAL  -> -10792.88993953 

COT( number )

Returns the cotangent of number . For example:

  SELECT COT(3.1415)  -> -10792.88993953 

CV( [ dimension_column ] )

Relevant only in the inter-row calculations performed within the MODEL clause of a SELECT statement, this function returns the current value of the dimension_column . CV can only be used in the righthand side of a rule, since it returns the value of the dimension_column from the left-hand side of the same rule.

DBTIMEZONE

Returns the time zone offset from UTC time for the database server. For example:

  SELECT DBTIMEZONE FROM DUAL  -> +00:00 

DECODE( expr, search , result [, search , result [,... n ]] [, default ] )

Compares expr to the search value; if expr is equal to a search , it returns the result. For example:

  DECODE ('B','A',1,'B',2,...'Z',26,'?')  -> 2 

Without a match, DECODE returns default , or NULL if default is omitted. Refer to Oracle documentation for more details. Consider using CASE instead, as CASE is part of the ANSI/ISO SQL standard.

DECOMPOSE( string [{CANONICAL COMPATIBILITY}])

Returns string decomposed into UNICODE code-points. The second argument specifies the type of decomposition performed. CANONICAL , which specifies the default behavior, allows the original UNICODE string to be recomposed.

DEPTH( number )

Returns the depth of the path specified by the UNDER_PATH condition in an XML query. See the Oracle SQL Reference for more information.

DEREF( expression )

Returns the object referenced by expression , where expression must return a REF to an object.

DUMP( expression [, return_format [, starting_at [, length ]]] )

Returns a VARCHAR2 value containing a datatype code, length in bytes, and internal representation of expression . The resulting value is returned in the format of return_format . For example:

  SELECT DUMP('abc', 1016) FROM DUAL  Typ=96 Len=3 CharacterSet=AL32UTF8: 61,62,63 

EMPTY_BLOB() , EMPTY_CLOB()

Returns an empty LOB locator that can be used to initialize a LOB variable. It can also be used to initialize a LOB column or attribute to empty in an INSERT or UPDATE statement.

EXISTSNODE( instance , xpath [ , namespace ] )

Returns 1 if applying the XPath query in xpath would return any nodes from instance ; otherwise, returns 0. The optional namespace parameter specifies the XML namespace in the query. For more information on XML queries, refer to the Oracle SQL Reference.

EXTRACT( instance , xpath [ , namespace ] )

Returns the XML nodes from instance returned by running the XPath query contained in the xpath parameter. The optional namespace parameter specifies the XML namespace in the query. For more information on XML queries, refer to the Oracle SQL Reference. For example:

  SELECT EXTRACT(XMLTYPE('<foo><bar>Hello, World!</bar></foo>'),   '/foo/bar') from DUAL  <bar>Hello, World!</bar> 

EXTRACTVALUE( instance , xpath [ , namespace ] )

Returns the value from an XML node returned by running the XPath query contained in the xpath parameter. The optional namespace parameter specifies the XML namespace in the query. For more information on XML queries, refer to the Oracle SQL Reference. For example:

  SELECT EXTRACTVALUE(XMLTYPE('<foo><bar>Hello, World!</bar></foo>'),   '/foo/bar') from DUAL  Hello, World! 

FIRST

An aggregate function that returns a specified value from the row that ranks first, given the order specified in the ORDER BY clause. Syntax:

   aggregate   (   aexpr   ) KEEP (DENSE_RANK FIRST ORDER BY   expr   [,...n]) 

where the syntax of expr is:

   expr   := [ASCDESC] [NULLS {FIRSTLAST}] 

The first ranking row following the order specified by expr will be used in the aggregate function aggregate . The aexpr is the expression passed to the aggregate function. For example:

  SELECT MAX(c1) KEEP (DENSE_RANK FIRST ORDER BY c2) FROM FIVE_NUMS  1 

FIRST_VALUE( expression IGNORE NULLS ) OVER ( window_clause )

Returns the first value in an ordered set of values. FIRST_VALUE is an analytic function. See the Section 4.3 earlier in this chapter for a detailed explanation of the window_clause . For example:

  SELECT FIRST_VALUE(col1) OVER () FROM NUMS  1 1 1 1 

FROM_TZ( timestamp , timezone )

Returns timestamp converted to a TIMESTAMP WITH TIME ZONE value, where timestamp is a TIMESTAMP value and timezone is a string in the TZH:TZM format. For example:

  SELECT FROM_TZ(TIMESTAMP '2004-04-15 23:59:59', '8:00') FROM DUAL  '15-APR-04 11.59.59 PM +08:00' 

GREATEST( expression [,...n] )

Returns the greatest of the list of expressions . All expressions after the first are implicitly converted to the datatype of the first expression before the comparison. For example:

  SELECT GREATEST(8,2,4) FROM DUAL  -> 8 

GROUP_ID()

Returns a positive value for each duplicate group returned by a query containing a GROUP BY clause. This function is useful in filtering out duplicate groups created when using CUBE , ROLLUP , or other GROUP BY extension (see GROUPING .)

GROUPING( column_name )

Returns 1 when a row is added by CUBE , ROLLUP , or other GROUP BY extensions; otherwise returns 0. For example:

  SELECT royalty, SUM(advance) 'total advance',   GROUPING(royalty) 'grp'   FROM titles   GROUP BY royalty WITH ROLLUP  royalty        total advance            grp  ---------      ---------------------    --- NULL           NULL                     0   10             57000.0000               0   12             2275.0000                0   14             4000.0000                0   16             7000.0000                0   24             25125.0000               0   NULL           95400.0000               1 

GROUPING_ID( column_name1 [ , column_name2 ,... ])

Returns the base-10 number that is equal to the binary value constructed by concatenating the GROUPING values on each of the parameters. GROUPING_ID is useful when returning a query containing multiple levels of aggregation created by GROUP BY expressions. Consider using the GROUPING_ID function instead of multiple GROUPING functions within one query. This function is a shorthand equivalent of:

 BIN_TO_NUM(GROUPING(   column_name1   ) [, GROUPING(   column_name2   ) ,...]) 

HEXTORAW( string )

Converts string containing hexadecimal digits into a raw value. For example:

  SELECT HEXTORAW('0FE') FROM DUAL  -> '00FE' 

INITCAP( string )

Returns string , with the first letter of each word in uppercase and all other letters in lowercase. For example:

  SELECT INITCAP('thomas jefferson') FROM DUAL  -> 'Thomas Jefferson' 

INSTR( string1 , string2 [, start_at [, occurrence]] )

Returns the position of string2 within string1 . INSTR searches string1 with a starting position of start_at (an integer) looking for the specified occurrence of string2 . For example:

  SELECT INSTR('foobar', 'o', 1, 1) FROM DUAL  -> 2 

Use INSTRB for bytes, INSTRC for UNICODE complete characters, INSTR2 for UNICODE UCS2 code points, and INSTR4 for UNICODE UCS4 code points.

ITERATION_NUMBER

Relevant only in the inter-row calculations performed within the MODEL clause of a SELECT statement, this function returns the number of times the rules within the MODEL clause have been executed while processing the query.

LAG( expression [, offset ][, default ] ) OVER ( window_clause )

An analytic function that provides access to more than one row of a table at the same time without a self join. LAG provides a "lagging" value in the result set that lags offset rows behind the current row. The default value is used for the first offset rows in the result set, since the "lagging" value is undefined for these rows. See Section 4.3 earlier in this chapter for a detailed explanation of the window_clause . For example:

  SELECT c1, LAG(c1, 2, 0) OVER (ORDER BY c1) FROM FIVE_NUMS  1        0 2        0 3        1 4        2 5        3 

LAST

Returns the row that ranks last given the order specified in the ORDER BY clause. The syntax is:

   aggregate   (   aexpr   ) KEEP (DENSE_RANK LAST ORDER BY   expr   [,...n]) 

where the syntax of expr is:

   expr   := [ASCDESC] [NULLS {FIRSTLAST}] 

The last ranking row following the order specified by expr will be used in the aggregate function aggregate . The aexpr is the expression passed to the aggregate function. For example:

  SELECT MIN(c1) KEEP (DENSE_RANK LAST ORDER BY c1) FROM FIVE_NUMS  5 

LAST_DAY( date )

Returns the date of the last day of the month that contains date . For example:

  SELECT LAST_DAY('15-APR-1999') FROM DUAL  -> 30-APR-99 

LAST_VALUE( expression [IGNORE NULLS]) OVER ( window_clause )

Returns the last value in an ordered set of values. See Section 4.3 earlier in this chapter for a detailed explanation of the window_clause . For example:

  SELECT c1, LAST_VALUE(c1) OVER (ORDER BY c1) FROM FIVE_NUMS  1        5 2        5 3        5 4        5 5        5 

LEAD( expression [, offset ][, default ] ) OVER( window_clause )

An analytic function that provides access to more than one row of a table at the same time without a self join. LEAD provides a "leading" value in the result set that is offset rows ahead of the current row. The default value is used for the last offset rows in the result set, since the "leading" value is undefined for these rows. See Section 4.3 earlier in this chapter for a detailed explanation of the window_clause . For example:

  SELECT c1, LEAD(c1, 2) OVER (ORDER BY c1) FROM FIVE_NUMS  1        3 2        4 3        5 4 5 

LEAST( expression [,...n] )

Returns the least of the list of expressions. For example:

  SELECT LEAST(10,5,3,7) FROM DUAL  -> 3 

LENGTH( string )

Returns the integer length of string , or NULL if string is NULL. For example:

  SELECT LENGTH('DUCK') FROM DUAL  -> 4 

LENGTHB( string )

Returns the length of char in bytes; otherwise, the same as LENGTH . For example:

  SELECT LENGTHB('DUCK') FROM DUAL  -> 4 

Use LENGTHB for bytes, LENGTHC for UNICODE complete characters, LENGTH2 for UNICODE UCS2 code points, and LENGTH4 for UNICODE UCS4 code points.

LNNVL( condition )

Returns true if condition is false or if one of the operands in condition is NULL. For example:

  SELECT COUNT(*) FROM authors WHERE LNNVL(contract <> 1)  -> 4 

LOCALTIMESTAMP [( precision )]

Returns a TIMESTAMP value for the current date and time. This function is similar to CURRENT_TIMESTAMP , with the exception that this function does not return a TIME ZONE value with the TIMESTAMP . For example:

  SELECT LOCALTIMESTAMP FROM DUAL  -> '15-APR-05 03.15.00 PM' 

LOG( base_number , number )

Returns the logarithm of any base_number of number . For example:

  SELECT LOG(50,10) FROM DUAL  -> .58859191 

LPAD( string1 , number [, string2 ] )

Returns string1 , left-padded to length number using characters in string2 ; string2 defaults to a single blank. For example:

  SELECT LPAD('ucks',5,'d') FROM DUAL  -> 'ducks' 

LTRIM( string [, set ] )

Removes all characters in set from the left of string . Set defaults to a single blank. For example:

  SELECT LTRIM('   Howdy!   ',' ') FROM DUAL  -> 'Howdy!   ' 

MAKE_REF( { table_name view_name } , key [,...n] )

Creates a reference ( REF ) to a row of an object view or a row in an object table whose object identifier is primary key-based.

MEDIAN( expression ) OVER ( partitioning )

Returns the median value in an ordered set of numeric or datetime values. See Section 4.3 earlier in this chapter for a detailed explanation of the partitioning clause. For example:

  SELECT MEDIAN(c1) FROM FIVE_NUMS  -> 3 

MONTHS_BETWEEN( date1 , date2 )

Returns the number of months between dates date1 and date2 . When date1 is later than date2 , the result is positive. If it is earlier, the result is negative. For example:

  SELECT MONTHS_BETWEEN('15-APR-2000', '15-JUL-1999') FROM DUAL  -> 9 

NANVL( a , b )

Returns b when a is not a number ( NaN ); returns a otherwise. Expression a must evaluate to a BINARY_FLOAT or BINARY_DOUBLE number, which are the only number types that permit storing NaN . For example:

  SELECT c1, NANVL(c1, 0) FROM NUMS  1.0E+000      1.0E+000 2.0E+000      2.0E+000 Nan           0 

NCHAR( number )

A synonym for CHR( number ) USING NCHAR_CS .

NEW_TIME( date , time_zone1 , time_zone2 )

Returns the date and time in time_zone2 using date as the input date/time, and using time_zone1 as the originating time zone. For example:

  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH12:MI:SS'   SELECT NEW_TIME(TO_DATE('04-15-99 08:22:31', 'MM-DD-YY HH12:MI:SS'),   'AST', 'PST') FROM DUAL  15-APR-2099 04:22:31 

Time_zone1 and Time_zone2 may be any of these text strings:

AST, ADT: Atlantic Standard or Daylight Time

BST, BDT: Bering Standard or Daylight Time

CST, CDT: Central Standard or Daylight Time

EST, EDT: Eastern Standard or Daylight Time

GMT: Greenwich Mean Time

HST, HDT: Alaska-Hawaii Standard or Daylight Time

MST, MDT: Mountain Standard or Daylight Time

NST: Newfoundland Standard Time

PST, PDT: Pacific Standard or Daylight Time

YST, YDT: Yukon Standard or Daylight Time

NEXT_DAY( date , string )

Returns the date of the first weekday named by string that is later than date . The argument string must be either the full name or the abbreviation of a day of the week in the date language of the session. For example:

  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'   SELECT NEXT_DAY('15-APR-1999', 'SUNDAY') FROM DUAL  18-APR-1999 

NLS_CHARSET_DECL_LEN( bytecnt , csid )

Returns the declaration width ( bytecnt ) of an NCHAR column using the character set ID ( csid ) of the column.

NLS_CHARSET_ID( text )

Returns the NLS character set ID number corresponding to text .

NLS_CHARSET_NAME( number )

Returns the VARCHAR2 name for the NLS character set corresponding to the ID number .

NLS_INITCAP( string [, nlsparameter ] )

Returns string with the first letter of each word in uppercase and all other letters in lowercase. The nlsparameter offers special linguistic sorting features.

NLS_LOWER( string [, nlsparameter ] )

Returns string with all letters lowercase. The nlsparameter offers special linguistic sorting features.

NLSSORT( string [, nlsparameter ] )

Returns the string of bytes used to sort string . The nlsparameter offers special linguistic sorting features.

NLS_UPPER( string [, nlsparameter ] )

Returns string with all letters uppercase. The nlsparameter offers special linguistic sorting features.

NTILE( expression ) OVER ([ partitioning ] ordering )

Divides an ordered data set into a number of groups numbered 1 to expression and assigns the appropriate group number to each row. Rows are allocated to each group so that the number of rows per group varies by no more than one. See Section 4.3 earlier in this chapter for details on the partitioning and ordering clauses. For example:

  SELECT c1, NTILE(4) OVER (ORDER BY c1) FROM FIVE_NUMS  1        1 2        1 3        2 4        3 5        4 

NULLIF( expr1 , expr2 )

Returns NULL if expr1 is equal to expr2 . If the two expressions are not equal, then the function returns expr1 . A NULL value, if passed as one of the expressions, can only be specified as the second expression expr2 . For example:

  SELECT c1, c2, NULLIF(c1, c2) FROM NUMS  1      2      1 2      2       3      2      3 

NUMTODSINTERVAL( number , string )

Converts number to an INTERVAL DAY TO SECOND literal, where number is a number or an expression resolving to a number, such as a numeric datatype column. The second argument, string , can be ' DAY ', ' HOUR ', ' MINUTE ', or ' SECOND ', which specifies how to interpret number . For example:

  SELECT NUMTODSINTERVAL(100, 'DAY') FROM DUAL  +000000100 00:00:00.000000000 

NUMTOYMINTERVAL( number , string )

Converts number to an INTERVAL YEAR TO MONTH literal, where number is a number or an expression resolving to a number, such as a numeric datatype column. The second argument, string , can be ' YEAR ' or ' MONTH ', which specifies how to interpret number . For example:

  SELECT NUMTOYMINTERVAL(100, 'YEAR') FROM DUAL  +000000100-00 

NVL( expression1 , expression2 )

If expression1 is NULL, expression2 is returned in place of that NULL value. Otherwise, expression1 is returned. expression1 and expression2 may be any datatype. For example:

  SELECT NVL(2,29) FROM DUAL  -> 2 

NVL2( expression1 , expression2 , expression3 )

Similar to NVL , except that if expression1 is not NULL, expression2 is returned. If expression1 is NULL, expression3 is returned. The expressions may be any datatype except LONG . For example:

  SELECT NVL2(1,3,5) FROM DUAL  -> 3 

ORA_HASH( expression [ , buckets [ , seed]] )

Computes a hash value from expression and returns a bucket number based on the computed hash value. The optional buckets argument is the maximum bucket number to use, which is one less than the total number of buckets, since the bucket numbering starts at 0. The default for buckets is 4,294,967,295. The optional seed value is used to seed the hashing function so that multiple results can be produced from the same data by only changing the seed value. The seed value defaults to 0. This example psuedo- randomly assigns all numbers to one of two buckets and returns those assigned to the first bucket, which will be a sample of roughly one half of the values:

  SELECT C1 FROM FIVE_NUMS WHERE   ORA_HASH(C1, 1, TO_CHAR(SYSTIMESTAMP, 'SSSS.FF')) = 0  1 5 

PATH( number )

Returns the path specified by the UNDER_PATH condition with the correlation variable number in an XML query. See the Oracle SQL Reference for more information.

POWERMULTISET( nested_table ) POWERMULTISET_BY_CARDINALITY( nested_table , cardinality )

Returns a nested table of nested tables of all nonempty subsets of the input nested table in the nested_table parameter. POWERMULTISET_BY_CARDINALITY has an additional parameter that can be used to limit the subsets returned to a specified minimum cardinality. For more information, please see the Oracle SQL Reference.

PRESENTNNV( cell_reference , expr1 , expr2 )

Relevant only in the inter-row calculations performed within the MODEL clause of a SELECT statement, this function returns expr1 when cell_reference exists and is not NULL; otherwise, it returns expr2 .

PRESENTV( cell_reference , expr1 , expr2 )

Relevant only in the interrow calculations performed within the MODEL clause of a SELECT statement. This function returns expr1 when cell_reference exists; otherwise, it returns expr2 .

PREVIOUS( cell_reference )

Relevant only in the inter-row calculations performed within the ITERATE...[UNTIL] section of a SELECT 's MODEL clause. This function returns the value held by cell_reference at the beginning of the iteration.

RATIO_TO_REPORT ( value_exprs ) OVER ( partitioning )

Computes the ratio of a value in value_exprs to the sum of all value_exprs with each partition. If values_expr is NULL, the ratio-to-report value also is NULL. See Section 4.3 earlier in this chapter for details on the partitioning clause. For example:

  SELECT c1, RATIO_TO_REPORT(c1) OVER () FROM FIVE_NUMS  1              .066666667 2              .133333333 3              .2 4              .266666667 5              .333333333 

RAWTOHEX( raw )

Converts a raw value to a string (character datatype) of its hexadecimal equivalent. For example:

  SELECT RAWTOHEX('Hi') FROM DUAL  -> 4869 

RAWTONHEX( raw )

Converts a raw value to an NVARCHAR2 (character datatype) of its hexadecimal equivalent.

REF( table_alias )

REF takes a table alias associated with a row from an object table or an object view. A special reference value is returned for the object instance that is bound to the variable or row.

REFTOHEX( expression )

Converts expression to a character value containing its hexadecimal equivalent.

REGEXP_INSTR( string , pattern [ , start_at [ , occurrence [ , roption [ , mparam ] ] ] ] )

Returns the character position within string matching the regular expression pattern in pattern . REGEXP_INSTR searches string with a starting position of start_at (an integer greater than 0) looking for the specified occurrence of pattern . Both the start_at and occurrence parameters default to 1. The roption parameter can be 0 or 1 and specifies if the position returned is the first character matching the pattern or the character after. The default for roption is 0, which returns the position of the first character matching the pattern. The mparam argument can be used to modify the matching behavior of the function and can be set to one or more of the following characters:

' i '

matching is case-insensitive

' c '

matching is case-sensitive

' n '

the "." character matches newline characters

' m '

treat input string as multiple lines; use ' ^ ' to match the beginning of a line and ' $ ' to match the end of a line

For example:

  SELECT REGEXP_INSTR('Hello, World!', '([^ ]*)!', 1, 1) FROM DUAL  -> 8 

REGEXP_REPLACE( string , pattern [ , newstr [ , start_at [ , occurrence [ , mparam ] ] ] ] )

Returns the result of replacing all occurrences of the regular expression in pattern within string with another string newstr . REGEXP_REPLACE searches string with a starting position of start_at (an integer greater than 0) looking for the specified occurrence of pattern . Both the start_at and occurrence parameters default to 1. The mparam argument can be used to modify the matching behavior of the function and can be set to one or more of the following characters:

' i '

matching is case-insensitive

' c '

matching is case-sensitive

' n '

the "." character matches newline characters

' m '

treat input string as multiple lines; use ' ^ ' to match the beginning of a line and ' $ ' to match the end of a line

For example:

  SELECT REGEXP_REPLACE('Hello, World!', '([^ ]*!)', 'Reader!') FROM DUAL  'Hello, Reader!' 

REGEXP_SUBSTR( string , pattern [ , start_at [ , occurrence [ , mparam ] ] ] )

Returns the substring within string matching the regular expression pattern pattern . REGEXP_SUBSTR searches string with a starting position of start_at (an integer greater than 0) looking for the specified occurrence of pattern . Both the start_at and occurrence parameters default to 1. The mparam argument can be used to modify the matching behavior of the function and can be set to one or more of the following characters:

' i '

matching is case-insensitive

' c '

matching is case-sensitive

' n '

the `.' character matches newline characters

' m '

treat input string as multiple lines; use ' ^ ' to match the beginning of a line and ' $ ' to match the end of a line

For example:

  SELECT REGEXP_SUBSTR('Hello, World!', '([^ ]*!)') FROM DUAL  'World!' 

REMAINDER( m , n )

Returns the remainder of m divided by n . This return value is equivalent to the expression:

 m-n*ROUND(m/n) 

The function MOD uses FLOOR instead of ROUND . For example:

  SELECT REMAINDER(11, 4), MOD(11, 4) FROM DUAL  -1       3 

REPLACE( string , search_string [, replacement_string ] )

Returns string with every occurrence of search_string replaced with replacement_string . For example:

  SELECT REPLACE('change', 'e', 'ing') FROM DUAL  -> 'changing' 

ROUND ( number [, decimal ] )

Returns number rounded to decimal places right of the decimal point. When decimal is omitted, number is rounded to an integer. Note that decimal , an integer, can be negative to round off digits left of the decimal point. For example:

  SELECT ROUND(12345.6789, 2) FROM DUAL  -> 12345.68 

ROUND ( date [, format ] )

Returns the date rounded to the unit specified by the format model. When format is omitted, date is rounded to the nearest day. (For more on valid format specifiers, see the TO_CHAR function.) For example:

  SELECT ROUND(TO_DATE('15-APR-1999'), 'MONTH') FROM DUAL  01-APR-1999 

ROWIDTOCHAR( rowid ), ROWIDTONCHAR( rowid )

ROWIDTOCHAR converts a rowid value to the VARCHAR2 datatype, 18 characters long. ROWIDTONCHAR converts rowid to an 18-character long NVARCHAR2 value. For example:

  SELECT ROWIDTOCHAR(ROWID) FROM NUMS  ABAsxDAAKAAAAEqAAA ABAsxDAAKAAAAEqAAB ABAsxDAAKAAAAEqAAC ABAsxDAAKAAAAEqAAD 

RPAD( string1 , number [, string2 ] )

Returns string1 , right-padded to length number with the value of string2 , repeated as needed. string2 defaults to a single blank. For example:

  SELECT RPAD('duck',8,'s') FROM DUAL  -> 'duckssss' 

RTRIM( string [, set ] )

Returns string , with all the rightmost characters that appear in set removed; set defaults to a single blank. For example:

  SELECT RTRIM('   welcome    ', ' ') FROM DUAL  -> '   welcome' 

SCN_TO_TIMESTAMP( scn )

Returns the timestamp associated with the System Change Number (SCN) argument. For example:

  SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM NUMS WHERE c1 = 1  15-APR-04 02.56.05.000000000 PM 

SESSIONTIMEZONE

Returns the session's time zone offset. For example:

  SELECT SESSIONTIMEZONE FROM DUAL  -> -06:00 

SET( nested_table )

Returns a nested table of distinct elements from the input nested table. For more information, please see the Oracle SQL Reference.

SIGN( number )

When number < 0, returns -1. When number = 0, returns 0. When number > 0, returns 1. For example:

  SELECT SIGN(-3.1415926) FROM DUAL  -> -1 

SIN( number )

Returns the sine of number , where number is in radians. For example:

  SELECT SIN(0)  -> 0.000000 

SINH( number )

Returns the hyperbolic sine of number . For example:

  SELECT SINH(180) FROM DUAL  -> 7.4469E+77 

SOUNDEX( string )

Returns a character string containing the phonetic representation of string . This function allows words that are spelled differently but sound alike in English to be compared for equality. For example:

  SELECT SOUNDEX('thimble') FROM DUAL  -> 'T514' 

STATS_BINOMIAL_TEST, STATS_CROSSTAB, STATS_F_TEST, STATS_KS_TEST, STATS_MODE, STATS_MW_TEST, STATS_ONE_WAY_ANOVA, STATS_T_TEST_ONE, STATS_T_TEST_PAIRED, STATS_T_TEST_INDEP, STATS_T_TEST_INDEPU, STATS_WSR_TEST

Oracle provides many sophisticated statistical functions. For further information on the STATS_* functions, please see the Oracle SQL Reference.

STDDEV( [ DISTINCT ALL ] expression ) [ OVER ( window_clause ) ]

Returns a sample standard deviation of a set of numbers shown as expression . See Section 4.3 earlier in this chapter for details on the window_clause . For example:

  SELECT STDDEV(col1) FROM NUMS  -> 5.71547607 

STDDEV_SAMP( expression ) [ OVER ( window_clause ) ]

Computes the cumulative sample standard deviation and returns the square root of the sample variance. See Section 4.3 earlier in this chapter for details on the window_clause . For example:

  SELECT STDDEV_SAMP(col1) FROM NUMS  -> 5.71547607 

STDEV_POP( expression ) [ OVER ( window_clause ) ]

Computes the population standard deviation and returns the square root of the population variance. See Section 4.3 earlier in this chapter for details on the window_clause . For example:

  SELECT STDDEV_POP(col1) FROM NUMS  -> 4.94974747 

SUBSTR( string , start [ FROM starting_position ] [ FOR length ] )

Refer to the earlier section on SUBSTR . For example:

  SELECT SUBSTR('Hello, World!',8,10) FROM DUAL  -> 'World!' 

SUBSTRB( extraction_string [ , length ] )

Returns the portion of string beginning at start and continuing for length characters. If length is omitted, all characters from start onward are returned. If start is negative, it represents an offset from the right edge of the string . For example:

 SELECT SUBSTR  ('Hello, World!', 8) FROM DUAL -> World! 

Use SUBSTRB for bytes, SUBSTRC for UNICODE complete characters, SUBSTR2 for UNICODE UCS2 code points, and SUBSTR4 for UNICODE UCS4 code points.

SYS_CONNECT_BY_PATH( column , char )

For hierarchical queries, SYS_CONNECT_BY_PATH returns the path from the root to the node with the column name specified by the column parameter. The char parameter specifies the node separator for the return path. For more on Oracle hierarchical queries, please refer to the Oracle SQL Reference.

SYS_CONTEXT( namespace , attribute [, length ] )

Returns the value of attribute associated with the context namespace , usable in both SQL and PL/SQL statements. For example:

  SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL  'LOGIN' 

SYS_DBURIGEN( column [ , rowid ] [,...] [ , ' text() '] )

Returns a URL that can be used as a unique reference to the row specified by the column parameter. For columns that don't hold unique values, a rowid can be used directly after the column it identifies to guarantee that the URL only points to one row. Use the ' text() ' option if you want the URL to point to the text within an XML document, instead of the document itself.

SYS_EXTRACT_UTC( datetime )

Returns the datetime argument converted to a UTC datetime value. For example:

  SELECT SYS_EXTRACT_UTC(TIMESTAMP '2004-04-15 11:59:59.00 -08:00')   FROM DUAL  '15-APR-04 07.59.59.000000000 PM' 

SYS_GUID()

Generates and returns a globally unique identifier ( RAW value) made up of 16 bytes. For example:

  SELECT SYS_GUID() FROM DUAL  -> C0FD3FDC30148EAEE030440A49096A41 

SYS_TYPEID( object_value )

Returns the type ID of the object_value parameter.

SYS_XMLAGG( expr [ , format ] )

Returns a single XML document created by aggregating the XML documents or fragments in the expr parameter. The optional format parameter can be used to format the XML document. For more information, refer to the Oracle SQL Reference.

SYS_XMLGEN( expression [ , format ] )

Returns a single XML document created from the expression . The optional format parameter can be used to format the XML document. For more information, refer to the Oracle SQL Reference.

SYSDATE

Returns the current date and time on the system in which the database is hosted. The value returned is of type DATE . For example:

  SELECT SYSDATE FROM DUAL  -> 26-JUN-2003 

SYSTIMESTAMP

Returns the current date and time on the system in which the database is hosted. The value returned is of type TIMESTAMP . For example:

  SELECT SYSTIMESTAMP FROM DUAL  26-JUN-2003 11.15.00.000000 PM -06:00 

TAN( number )

Returns the tangent of number , where number is in radians. For example:

  SELECT TAN(3.1415) FROM DUAL  -> -0.000093 

TANH( number )

Returns the hyperbolic tangent of number . For example:

  SELECT TANH(180) FROM DUAL  -> 1 

TIMESTAMP_TO_SCN ( timestamp_value )

Returns the approximate system change number ( SCN ) associated with that timestamp. The return value is of type NUMBER .

TO_BINARY_DOUBLE ( expr [, format [, nls_parameter ]] )

Converts expr to a BINARY_DOUBLE in the format specified by the format parameter. If expr is a character expression, the format and nls_parameter options have equivalent meanings, as they do in the TO_CHAR function. If expr is a numeric expression, then format and nls_parameter must be omitted. For example:

  SELECT c1, TO_BINARY_DOUBLE(c1) FROM NUMS  1             1.0E+000 2             2.0E+000 3             3.0E+000 

TO_BINARY_FLOAT( expr [, format [, nls_parameter ]] )

Converts expr to a BINARY_FLOAT in the format specified by the format parameter. If expr is a character expression, the format and nls_parameter options have equivalent meanings as they do in the TO_CHAR function. If expr is a numeric expression, then format and nls_parameter must be omitted. For example:

  SELECT c1, TO_BINARY_FLOAT(c1) FROM NUMS  1             1.0E+000 2             2.0E+000 3             3.0E+000 

TO_CHAR ( character_expr )

Converts character_expr to the database character set. For example:

  SELECT TO_CHAR('Howdy') FROM DUAL  Howdy 

TO_CHAR ( date interval [, format [, nls_parameter ]] )

Converts date or interval to a VARCHAR2 in the format specified by the date format . When format is omitted, date is converted to the default date format. The nls_parameter option offers additional control over formatting. Listed below are the available specifiers for format and their meanings.

AD or A.D.

AD indicator

AM or A.M.

Meridian indicator

BC or B.C.

BC indicator

D

Day of week (1-7)

DAY

Name of day

DD

Day of month (1-31)

DDD

Day of year (1-366)

DL

Long date format

DS

Short date format

DY

Abbreviated name of day

FF

Fractional seconds; to specify the precision, include a number (1-9) after the FF specifier

HH or HH12

Hour of day (1-12)

HH24

Hour of day (0-23)

J

Julian day; the number of days since January 1, 4713 BC

MI

Minute (0-59)

MM

Month (01-12)

MON

Abbreviated name of month

RM

Roman numeral month (I-XII)

SS

Second (0-59)

SSSSS

Seconds past midnight (0-86,399)

SYYY

Four-digit year; BC dates are prefixed with a minus sign

TS

Short time format.

TZD

Daylight savings information (example: PST versus PDT)

TZH

Time zone hour

TZM

Time zone minute

TZR

Time zone region

X

Local radix character

Y, YY, or YYY

One, two, or three digits for the year

Y,YYY

Year with comma

YYYY

Four-digit year

For example:

  SELECT TO_CHAR(TO_DATE('15-APR-1999') ,'MON-DD-YYYY') FROM DUAL  APR-15-1999 

TO_CHAR ( number [, format [, nls_parameter ]] )

Converts number to a VARCHAR2 in the format specified. When format is omitted, number is converted to a string long enough to hold the number. The nls_parameter option offers additional control over formatting options. For example:

  SELECT TO_CHAR(123.45, '9.99') FROM DUAL  -> 3.45 

TO_CLOB ( expr )

Converts the character expression given by expr to the CLOB datatype. For example:

  SELECT LENGTH(TO_CLOB('I am a SQL nut!')) FROM DUAL  -> 15 

TO_DATE( string [, format [, nls_parameter ]] )

Converts string (in CHAR or VARCHAR2 ) to a DATE datatype. The nls_parameter option offers additional control over formatting options. For example:

  SELECT TO_DATE('15/04/1999', 'DD/MM/YYYY') FROM DUAL  15-APR-1999 

TO_DSINTERVAL( string [, nls_parameter ] )

Converts the character expression given by string to the INTERVAL DAY TO SECOND datatype. The nls_parameter option offers additional control over formatting options. For example:

  SELECT CURRENT_DATE, CURRENT_DATE-TO_DSINTERVAL('14 00:00:00') FROM DUAL  15-APR-2003      01-APR-2003 

TO_LOB( long_column )

Usable only by LONG or LONG RAW expressions, it converts LONG or LONG RAW values in the column long_column to LOB values. It is usable only in the SELECT list of a subquery in an INSERT statement.

TO_MULTI_BYTE( string )

Returns string with all of its single-byte characters converted to their corresponding multibyte characters.

TO_NCHAR( expr [, format [, nls_parameter ]] )

Synonymous with the TO_CHAR function, except the return datatype is NCHAR . For more on valid format specifiers, see the TO_CHAR function.

TO_NCLOB ( expr )

Converts the character expression given by expr to the NCLOB datatype. For example:

  SELECT LENGTH(TO_NCLOB('I am a SQL nut!')) FROM DUAL  -> 15 

TO_NUMBER( string [, format [, nls_parameter ]] )

Converts a numeric string (of CHAR or VARCHAR2 datatype) to a value of NUMBER datatype, optionally in the format specified by the format model. The nls_parameter option offers additional control over formatting options. For example:

  SELECT TO_NUMBER('12345') FROM DUAL  -> 12345 

TO_SINGLE_BYTE( string )

Returns string with all of its multibyte characters converted to their corresponding single-byte characters.

TO_TIMESTAMP( string [, format [, nls_parameter ]] )

Converts the character expression provided by string to the TIMESTAMP datatype, optionally in the format specified by the format parameter. The nls_parameter option offers additional control over formatting options. (For more on valid format specifiers, see the TO_CHAR function.) For example:

  SELECT TO_TIMESTAMP(CURRENT_DATE) FROM DUAL  -> 04-MAY-04 12.00.00 AM 

TO_TIMESTAMP_TZ( string [, format [, nls_parameter ]] )

Converts the character expression provided by string to the TIMESTAMP WITH TIME ZONE datatype, optionally in the format specified by the format parameter. The nls_parameter option offers additional control over formatting options. (For more on valid format specifiers, see the TO_CHAR function.) For example:

  SELECT TO_TIMESTAMP_TZ('15-04-2006', 'DD-MM-YYYY') FROM DUAL  15-APR-06 12.00.00.000000000 AM -07:00 

TO_YMINTERVAL( string )

Converts the character expression provided by string to the INTERVAL YEAR TO MONTH datatype. For example:

  SELECT TO_DATE('29-FEB-2000')+TO_YMINTERVAL('04-00') FROM DUAL  29-FEB-04 

TRANSLATE( char_value , from_text , to_text )

Returns char_value with each occurrence of a character in from_text replaced by its corresponding character in to_text . For example:

  SELECT TRANSLATE('foobar', 'fa', 'bu') FROM DUAL  -> 'boobur' 

TRANSLATE ( text USING [ CHAR_CS NCHAR_CS ] )

Converts text into the character set specified. Use CHAR_CS to convert text to the CHAR datatype or NCHAR_CS to convert text to the NCHAR datatype. For example:

  SELECT TRANSLATE(N'foobar' USING CHAR_CS) FROM DUAL  'foobar' 

TREAT ( expr AS [ REF ] [ schema . ] type )

Converts expr from its declared type to the type specified in the type parameter. For more information on the usage of this function, please look to the SQL Reference for the Oracle Database.

TRUNC ( base [, number ] )

Returns base truncated to number decimal places. When number is omitted, base is truncated to an integer. number can be negative to truncate (make zero) digits left of the decimal point. (For more on valid format specifiers, see the TO_CHAR function.)For example:

  SELECT TRUNC('123.456', 2) FROM DUAL  -> 123.45 

TRUNC ( date [, format ] )

Returns date truncated to the unit specified by format . When format is omitted, date is truncated to the nearest whole day. (For more on valid format specifiers, see the TO_CHAR function.) For example:

  SELECT TRUNC(TO_DATE('15/04/1999', 'MM/DD/YYYY'), 'YYYY')   FROM DUAL  1999 

See TO_CHAR for a list of format specifiers.

TZ_OFFSET ( { expr SESSIONTIMEZONE DBTIMEZONE } )

Returns the time zone offset corresponding to the argument. The character expression expr can either be the name of the time zone or a time zone offset. The SESSIONTIMEZONE and DBTIMEZONE arguments provide the time zone for the session or database, respectively. For example:

  SELECT TZ_OFFSET('+08:00'), TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL  +08:00       -07:00 

UID

Returns an integer that uniquely identifies the currently logged on session user. No parameters are needed. For example:

  SELECT UID FROM DUAL  -> 47 

UNISTR( string )

Converts string to the NCHAR datatype while converting any UNICODE encoding values within string . For example:

  SELECT UNISTR('El Ni 
  SELECT UNISTR('El Ni\00F1o') FROM DUAL  -> 'El Ni ±o' 
 F1o') FROM DUAL  -> 'El Nio' 

UPDATEXML( instance , xpath , expr [ , namespace ] )

Updates the values held by nodes within instance to the new value in expr . Only those nodes returned by the XPath query contained in the xpath parameter are updated. The optional namespace parameter specifies the XML namespace in the query. For more information on XML queries, refer to the Oracle SQL Reference. For example:

  SELECT UPDATEXML(XMLTYPE('<foo><bar>Hello, World!</bar></foo>'),   '/foo/bar', '<bar>Bye, World!</bar>') from DUAL  Bye, World! 

USERENV( option )

Returns information about the current session in VARCHAR2 . This function has been deprecated and is only provided for backward compatibility. USERENV is a synonym for SYS_CONTEXT( ' USER_ENV' , option ) . Look to the SYS_CONTEXT function with the USERENV namespace for current functionality. For example:

  SELECT USERENV('LANGUAGE') "Language" FROM DUAL  'AMERICAN_AMERICA.AL32UTF8' 

VALUE( table_alias )

Takes a table_alias associated with a row in an object table and returns the object instance stored within the object table for that row.

VARIANCE( [ DISTINCT ] expression ) [ OVER ( window_clause ) ]

Returns the variance of expression calculated as follows : 0 if the number of rows in expression = 1; VAR_SAMP if the number of rows in expression > 1. See Section 4.3 earlier in this chapter for details on the window_clause . For example:

  SELECT VARIANCE(col1) FROM NUMS  -> 32.6666667 

VAR_POP( expression ) [ OVER ( window_clause ) ]

Returns the population variance of the set of numbers represented by expression after discarding the NULLs in the set. See Section 4.3 earlier in this chapter for details on the window_clause . For example:

  SELECT VAR_POP(col1) FROM NUMS  -> 24.5 

VAR_SAMP( expression ) [ OVER ( window_clause ) ]

Returns the sample variance of the set of numbers represented by expression after discarding the NULLs in the set. See Section 4.3 earlier in this chapter for details on the window_clause . For example:

  SELECT VAR_SAMP(col1) FROM NUMS  -> 32.6666667 

VSIZE( expression )

Returns the number of bytes in the internal representation of expression . When expression is NULL , returns NULL . For example:

  SELECT vsize(1) FROM DUAL  -> 2 

XMLAGG( instance [ , order_by ] )

An aggregate function that returns an XML document from a table of XML fragments contained in instance . The optional order_by clause allows the XML fragments in the result to be ordered. For more information on XML queries refer to the Oracle SQL Reference.

XMLCOLATTVAL( expr [ AS alias ] [,...] )

Returns an XML fragment from the expr arguments. The optional AS clause can be used to change the value of the name attribute. For more information on XML queries, refer to the Oracle SQL Reference.

XMLCONCAT( instance [,...] )

Returns an XML instance that is the union of all XML instance parameters. For more information on XML queries refer to the Oracle SQL Reference.

XMLELEMENT( [ NAME ] name [ , XMLATTRIBUTES( expr [ AS alias ] [,...] ) ] [ , value [,...]] )

Returns an XMLELEMENT with the name specified in the name parameter and attributes specified in the optional XMLATTRIBUTES clause. The value parameters provide the values of the XMLELEMENT result. For more information on XML queries refer to the Oracle SQL Reference.

XMLFOREST( value [ AS alias ] [,...] )

Returns an XML fragment constructed from the values provided in the value parameters. The optional AS clause can be used to change the enclosing tag name. For more information on XML queries refer to the Oracle SQL Reference.

XMLSEQUENCE( instance )

Returns an array of XML fragments constructed from the top-level nodes in the XML instance provided by the instance parameter. For more information on XML queries, refer to the Oracle SQL Reference.

XMLTRANSFORM( instance , stylesheet )

Returns the result of applying the XSL stylesheet in the stylesheet parameter to the XML document contained in instance . For more information on XML queries, refer to the Oracle SQL Reference.

4.5.4 PostgreSQL-Supported Functions

This section lists the functions specific to PostgreSQL, with examples and corresponding results.

ABSTIME( timestamp )

Converts the timestamp value to ABSTIME type. This function is provided for backwords compatibility and may be removed in future versions. For example:

  SELECT ABSTIME(CURRENT_TIMESTAMP)  -> 2003-06-24 00:19:17-07 

ACOS( number )

Returns the arc cosine of number ranging from -1 to 1. The result ranges from 0 to and is expressed in radians. For example:

  SELECT ACOS(0)  -> 1.570796 

AGE( timestamp )

Same meaning as AGE(CURRENT_DATE, timestamp ) .

AGE( timestamp , timestamp )

Returns the time between the two timestamp values. For example:

  SELECT AGE('2003-12-31', CURRENT_TIMESTAMP)  6 mons 7 days 00:34:41.658325 

AREA( object )

Returns the area of an item. For example:

  SELECT AREA(BOX '((0,0),(1,1))')  -> 1 

ASCII( text )

Returns the ASCII code of the first character of text . For example:

  SELECT ASCII('x')  -> 120 

ASIN( number )

Returns the arc sine of number ranging from -1 to 1. The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ASIN(0)  -> 0.000000 

ATAN( number )

Returns the arctangent of number . The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ATAN(3.1415)  -> 1.262619 

ATAN2( float1 , float2 )

Returns the arctangent of the two float values. ATAN2( x , y ) is similar to ATAN( y / x ) , with the exception that the signs of x and y are used to determine the quadrant of the result. For example:

  SELECT ATAN2(3.1415926, 0)  -> 1.5707963267949 

BOX( box , box )

Returns a BOX created by the intersection of the two boxes. If the two boxes do not intersect, the return value is NULL. For example:

  SELECT BOX(BOX '((-1,-1),(1,1))', BOX '((0,0),(1,1))')  (1,1),(0,0) 

BOX( circle )

Returns a BOX with vertices that intersect circle so that the box is the maximum size that can be fully contained within circle . For example:

  SELECT BOX(CIRCLE '((0,0),2.0)')  (1.41421356237309,1.41421356237309), (-1.41421356237309,-1.41421356237309) 

BOX( point , point )

Returns a BOX with the two point arguments as opposite corners. For example:

  SELECT BOX(POINT(0,0), POINT(1,1))  -> (1,1),(0,0) 

BOX( polygon )

Converts polygon to a BOX . For example:

  SELECT BOX(POLYGON '((0,0),(1,1),(2,0))')  -> (2,1),(0,0) 

BROADCAST( inet )

Constructs a broadcast address as text. For example:

  SELECT BROADCAST('192.168.1.5/24')  -> '192.168.1.255/24' 

CBRT( float8 )

Returns the cube root of float8 . For example:

  SELECT CBRT(8)  -> 2 

CENTER( object )

Returns a POINT object to the center of the argument. For example:

  SELECT CENTER(CIRCLE '((0,0), 2.0)')  -> (0,0) 

CHAR( text )

Converts text to the CHAR type.

CHAR_LENGTH( string ) or CHARACTER_LENGTH( string )

Returns the length of string in characters.

CIRCLE( box )

Returns a CIRCLE contained within box . For example:

  SELECT CIRCLE(BOX '((0,0),(1,1))')  -> <(0.5,0.5),0.707106781186548> 

CIRCLE( point , float8 )

Converts point to a CIRCLE with float8 for the radius. For example:

  SELECT CIRCLE(POINT '(0,0)', 2.0)  -> <(0,0),2> 

COALESCE( list )

Returns the first non-NULL value in list . For example:

  SELECT COALESCE(NULL,1,2,3,NULL)  -> 1 

COS( number )

Returns the cosine of number as an angle expressed in radians. For example:

  SELECT COS(0)  -> 1.000000 

COT( number )

Returns the cotangent of number . For example:

  SELECT COT(3.1415)  -> -10792.88993953 

DATE_PART( text , value )

Equivalent to EXTRACT( text , value ) ; for more details on the usage of EXTRACT , see the section about EXTRACT in Section 4.4.

DATE_TRUNC( precision , timestamp )

Truncates timestamp to the specified precision . For example:

  SELECT DATE_TRUNC('hour', TIMESTAMP '2003-04-15 23:58:30')  2003-04-15 23:00:00 

DEGREES( float8 )

Converts radians to degrees. For example:

  SELECT DEGREES(3.1415926)  -> 179.999996929531 

DIAMETER( circle )

Returns the diameter of circle . For example:

  SELECT DIAMETER(CIRCLE(POINT '(0,0)', 2.0))  -> 4 

FLOAT( int )

Converts int to a floating point.

FLOAT4( int )

Converts int to a floating point.

HEIGHT( box )

Returns the vertical size of box . For example:

  SELECT HEIGHT(BOX '((0,0),(1,1))')  -> 1 

HOST( inet )

Extracts the host address as text. For example:

  SELECT HOST('192.168.1.5/24')  -> '192.168.1.5' 

INITCAP( text )

Converts the first letter of each word to uppercase. For example:

  SELECT INITCAP('my name is inigo montoya.')  'My Name Is Inigo Montoya.' 

INTEGER( float )

Converts a floating point to integer.

INTERVAL( reltime )

Converts reltime to an INTERVAL .

ISCLOSED( path )

Returns ' t ' if path is closed, ' f ' if open . For example:

  SELECT ISCLOSED(PATH '((0,0),(1,1),(2,0))')  -> 't'  SELECT ISCLOSED(PATH '[(0,0),(1,1),(2,0)]')  -> 'f' 

ISFINITE( interval )

Returns ' f ' if interval is open, ' t ' otherwise. For example:

  SELECT ISFINITE(INTERVAL '4 hours')  -> 't' 

ISFINITE( timestamp )

Returns ' f ' if timestamp is either invalid or infinite, ' t ' otherwise. For example:

  SELECT ISFINITE(TIMESTAMP '2001-02-16 21:28:30')  -> 't' 

ISOPEN( path )

Returns an open path . For example:

  SELECT ISOPEN(PATH '((0,0),(1,1),(2,0))')  -> 'f'  SELECT ISOPEN(PATH '[(0,0),(1,1),(2,0)]')  -> 't' 

LENGTH( object )

Returns the length of object . For example:

  SELECT LENGTH('Howdy!')  -> 6  SELECT LENGTH(PATH '((-1,0),(1,0))')  -> 4 

LOG( float8 )

Returns a base-10 logarithm. For example:

  SELECT LOG(100)  -> 2 

LPAD( text , int , text )

Returns a left-pad string to the specified length. For example:

  SELECT LPAD('Duck', 10, 's')  -> 'ssssssDuck' 

LSEG( box )

Converts a box diagonal to a line segment. For example:

  SELECT LSEG(BOX '((-1,0),(1,0))')  -> [(1,0),(-1,0)] 

LSEG( point , point )

Converts points to a line segment. For example:

  SELECT LSEG(POINT '(-1,0)', POINT '(1,0)')  -> [(-1,0),(1,0)] 

LTRIM( text )

Returns text with all leading whitespace removed. For example:

  SELECT LTRIM('   Howdy!   ')  -> 'Howdy!   ' 

MASKLEN( cidr )

Returns the netmask length in cidr . For example:

  SELECT MASKLEN('192.168.1.5/24')  -> 24 

NETMASK( inet )

Returns the netmask for inet . For example:

  SELECT NETMASK('192.168.1.5/24')  -> '255.255.255.0' 

NETWORK( inet )

Returns the network part of inet . For example:

  SELECT NETWORK('192.168.1.5/24')  -> '192.168.1.0/24 

NPOINTS( object )

Returns the number of points in object . For example:

  SELECT NPOINTS(POLYGON '((1,1),(0,0))')  -> 2 

NULLIF( input , value )

Returns NULL if input = value , else returns input . For example:

  SELECT NULLIF(5, 6), NULLIF(5, 5)  5     NULL 

PATH( polygon )

Converts polygon to a path. For example:

  SELECT PATH('((0,0),(1,1),(2,0))')  ((0,0),(1,1),(2,0)) 

PCLOSE( path )

Converts path to a closed path. For example:

  SELECT PCLOSE(PATH '[(0,0),(1,1),(2,0)]')  ((0,0),(1,1),(2,0)) 

PI()

Returns the constant pi.

POLYGON( path )

Converts path to a POLYGON . For example:

  SELECT POLYGON(PATH '((0,0),(1,1),(2,0))')  ((0,0),(1,1),(2,0)) 

POINT( circle )

Returns the center of circle . For example:

  SELECT POINT(CIRCLE '((0,0), 2.0)')  -> (0,0) 

POINT( lseg1 , lseg2 )

Returns the intersection of two line segments. For example:

  SELECT POINT(LSEG '((-1,0),(1,0))', LSEG '((-2,-2),(2,2))')  (0,0) 

POINT( polygon )

Returns the center point of polygon . For example:

  SELECT POINT(POLYGON '((0,0),(1,1),(2,0))')  (1,0.333333333333333) 

POLYGON( box )

Returns a 4-point polygon. For example:

  SELECT POLYGON(BOX '((0,0),(1,1))')  ((0,0),(0,1),(1,1),(1,0)) 

POLYGON( circle )

Synonym for POLYGON( 12, circle ) .

POLYGON( npts , circle )

Returns and approximation of circle as a polygon with npts vertices. For example:

  SELECT POLYGON(6, CIRCLE '((0,0),2.0)')  ((-2,0),  (-0.999999999994107,1.73205080757228),  (1.00000000001179,1.73205080756207),  (2,-2.04136478690279e-11),  (0.999999999976428,-1.73205080758249),  (-1.00000000002946,-1.73205080755187)) 

POPEN( path )

Converts path to an open path. For example:

  SELECT POPEN(PATH '((0,0),(1,1),(2,0))')  [(0,0),(1,1),(2,0)] 

POW ( number , exponent )

Raises a number to the specified exponent . For example:

  SELECT POW(2, 3)  -> 8 

RADIANS( float8 )

Converts degrees to radians. For example:

  SELECT RADIANS(180)  -> 3.14159265358979 

RADIUS( circle )

Returns the radius of circle . For example:

  SELECT RADIUS(CIRCLE '((0,0), 2.0)')  -> (0,0) 

RELTIME( interval )

Converts interval to a RELTIME . Provided for backward compatibility and may be removed in a future release.

ROUND( number )

Rounds number to the nearest integer. For example:

  SELECT ROUND(5.5)  -> 6 

RPAD( text , length , char )

Pads text to the specified length using char . For example:

  SELECT RPAD('Duck', 10, 's')  -> 'Duckssssss' 

RTRIM( text )

Returns text with all trailing whitespace removed. For example:

  SELECT RTRIM('   St. Lucia   ')  -> '   St. Lucia' 

SET_MASKLEN( inet , size )

Sets the netmask length for inet to size . For example:

  SELECT SET_MASKLEN('192.168.1.5/24',16)  '192.168.1.5/16' 

SIN( number )

Returns the sine of number , where number is in radians. For example:

  SELECT SIN(0)  -> 0.000000 

SUBSTRING( string [ FROM start ] [ FOR bytes ] ) , SUBSTR( string , start [, bytes ] )

Extracts a substring of length bytes from string starting at the character position start . If bytes is omitted, then the length returned is the remainder of the string from the start position. For example:

  SELECT SUBSTRING('Inigo Montoya' FROM 7 FOR 4)  -> 'Mont' 

TAN( number )

Returns the tangent of number , where number is in radians. For example:

  SELECT TAN(3.1415)  -> -0.000093 

TEXT( char )

Converts char to TEXT type.

TIMESTAMP( date [,time] )

Converts date to a timestamp.

TO_CHAR( expression , text )

Converts expression to a string. For example:

  SELECT TO_CHAR(NUMERIC '-125.8', '999D99S')  -> 125.80-  SELECT TO_CHAR (interval '15h 2m 12s','HH24:MI:SS')  -> 15:02:12 

TO_DATE( string , format)

Converts string to a date using the second argument for the input format. Listed below are the available specifiers for format and their meanings.

AM or A.M.

Meridian indicator

AD or A.D.

AD indicator

BC or B.C.

BC indicator

CC

Two-digit century

D

Day of week (1-7)

DD

Day of month (01-31)

DDD

Day of year (001-366)

DAY

Full uppercase day name

Day

Full camel case day name

day

Full lowercase day name

DY

Abbreviated uppercase day name

Dy

Abbreviated camel case day name

dy

Abbreviated lowercase day name

HH or HH12

Hour of day (01-12)

HH24

Hour of day (00-23)

IW

ISO week number of year

J

Julian day number (days since January 1, 4713 BC)

MI

Minute of hour (00-59)

MM

Two-digit month number (01-12)

MON

Abbreviated uppercase month name

Mon

Abbreviated camel case month name

mon

Abbreviated lowercase month name

MONTH

Full uppercase month name

Month

Full camel case month name

month

Full lowercase month name

MS

Milliseconds (000-999)

PM or P.M.

Meridian indicator

Q

Quarter of the year

RM

Roman Numeral month (I-XII)

rm

Lowercase Roman Numeral month (i-xii)

SS

Seconds (00-59)

SSSS

Seconds past midnight (0-86399)

TZ

Uppercase time zone name

tz

Lowercase time zone name

US

Microseconds (000000-999999)

W

Week of month (1-5)

WW

Week of year (1-53)

Y, YY, or YYY

One-, two-, or three-digit years

Y,YYY

Year with comma

YYYY

Four-digit year

For example:

  SELECT TO_DATE('05 Dec 2000', 'DD Mon YYYY')  -> 2000-12-05 

TO_NUMBER( string , format )

Converts string to a numeric value using the second argument for the input format. For example:

  SELECT TO_NUMBER('12,454.8-', '99G999D9S')  -> -12454.8 

TO_TIMESTAMP( text , format )

Converts text to a timestamp value using the second argument for the input format. (For more on valid format specifiers, see the TO_DATE function.) For example:

  SELECT TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY')  -> 2000-12-05 00:00:00-08 

TRANSLATE( text , from , to )

Converts the characters found in text that also exist in from to the corresponding characters in to . For example:

  SELECT TRANSLATE('foo', 'fo', 'ab')  -> 'abb' 

TRUNC( float8 )

Truncates (towards zero). For example:

  SELECT TRUNC(PI())  -> 3 

VARCHAR( string )

Converts string to a VARCHAR .

WIDTH( box )

Returns the width of box . For example:

  SELECT WIDTH(BOX '((0,0),(3,1))')  -> 3 

4.5.5 SQL Server-Supported Functions

This section provides an alphabetical listing of Microsoft SQL Server-supported functions, with examples and corresponding results.

ACOS( number )

Returns the arc cosine of number ranging from -1 to 1. The result ranges from 0 to and is expressed in radians. For example:

  SELECT ACOS(0)  -> 1.570796 

APP_NAME()

Returns the application name for the current session, set by the application. For example:

  SELECT APP_NAME()  -> 'SQL Enterprise Manager' 

ASCII( text )

Returns the ASCII code of the first character of text . For example:

  SELECT ASCII('x')  -> 120 

ASIN( number )

Returns the arc sine of number ranging from -1 to 1. The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ASIN(0)  -> 0.000000 

ATAN( number )

Returns the arctangent of any number . The resulting value ranges from -/2 to /2 and is expressed in radians. For example:

  SELECT ATAN(3.1415)  -> 1.262619 

ATN2( float1 , float2 )

Returns the angle (in radians) whose tangent is float1 / float2 . For example:

  SELECT ATN2(35.175643, 129.44)  -> 0.265345 

BINARY_CHECKSUM( * expression [,... n ] )

Returns the binary checksum for a list of expressions or for a row of a table. This example returns a list of user IDs where the stored password checksum doesn't match the current password's checksum:

  SELECT userid AS 'Changed' FROM users WHERE NOT password_chksum =   BINARY_CHECKSUM(password)  

CHAR( integer_expression )

Converts a numeric ASCII code to a character. For example:

  SELECT CHAR(78)  -> 'N' 

CHARINDEX( substring , string [, start_location ] )

Returns the position of the first occurrence of a substring in a string. For example:

  SELECT CHARINDEX('he', 'Howdy, there!')  -> 9 

CHECKSUM( * expression [,...n] )

Returns a checksum (computed over row values or expressions provided). The following example returns a list of user IDs for which the stored password checksum doesn't match the current password's checksum:

  SELECT userid AS 'Changed' FROM users WHERE NOT password_chksum =   BINARY_CHECKSUM(password)  

CHECKSUM_AGG( [ ALL DISTINCT ] expression )

Returns the checksum of the values in a group. For example:

  SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM authors  -> 67 

COALESCE( expression [,...n] )

Returns the first non-NULL argument from a list of arguments. For example:

  SELECT COALESCE(NULL, 1, 3, 5, 7)  -> 1 

COL_LENGTH( table , column )

Returns column length in bytes. For example:

  SELECT COL_LENGTH('authors', 'au_fname')  -> 50 

COL_NAME( table_id , column_id )

Returns column name, given table _id and column _id . For example:

  SELECT COL_NAME(OBJECT_ID('authors'), 1)  

CONTAINS( { column * }, contains_search_condition )

Searches columns for exact or "fuzzy" matches of the contains_seach_criteria . CONTAINS is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information. This example returns all product ID's from the products table that contain the words " peanut " and "butter" in close proximity to each other.

  SELECT productid FROM products   WHERE CONTAINS(productname, ' "peanut" NEAR "butter" ')  

CONTAINSTABLE( table , column , contains_search_condition )

Returns a table with exact and "fuzzy" matches to contains_search_condition. CONTAINSTABLE is an elaborate function used to perform full-text searches. The following example returns all product ID's from the products table that contain the words "peanut" and "butter" in close proximity to each other:

  SELECT productid FROM products WHERE CONTAINS   (products, productname, ' "peanut" NEAR "butter" ')  

CONVERT( data_type [ ( length ) ], expression [, style ] )

Converts data from one datatype to another. For example:

  SELECT CONVERT(VARCHAR(50), CURRENT_TIMESTAMP, 1)  -> '06/29/03' 

COS( number )

Returns the cosine of number as an angle expressed in radians. For example:

  SELECT COS(0)  -> 1.000000 

COT( number )

Returns the cotangent of number . For example:

  SELECT COT(3.1415)  -> -10792.88993953 

DATABASEPROPERTYEX ( database , property )

Returns a database option or property. For example:

  SELECT DATABASEPROPERTYEX('pubs', 'Version')  -> 539 

DATALENGTH( expression )

Returns the number of bytes in a character or binary string. For example:

  SELECT MAX(DATALENGTH(au_fname)) FROM authors  -> 11 

DATEADD( datepart , number , date )

Adds a number of dateparts (e.g., days) to a datetime value. For example:

  SELECT DATEADD(Year, 10, CURRENT_TIMESTAMP)  -> 2013-06-29 19:47:15.270 

DATEDIFF( datepart , startdate , enddate )

Calculates the difference between two datetime values expressed in the specified datepart . For example:

  SELECT DATEDIFF(Day, CURRENT_TIMESTAMP,   DATEADD(Year, 1, CURRENT_TIMESTAMP))  366 

DATENAME( datepart , date )

Returns the name of a datepart (e.g., month) of a datetime argument. For example:

  SELECT DATENAME(month, GETDATE())  -> 'June' 

DATEPART( datepart , date )

Returns the value of a datepart (e.g., hour) of a datetime argument. For example:

  SELECT DATEPART(year, GETDATE())  -> 2003 

DAY( date )

Returns an integer value representing the day of the date provided as a parameter. For example:

  SELECT DAY('04/15/2004')  -> 15 

DB_ID([ database_name ] )

Returns a database ID and given name. For example:

  SELECT DB_ID()  -> 5 

DB_NAME( database_id )

Returns the database name. For example:

  SELECT DB_NAME(5)  -> 'pubs' 

DEGREES( numeric_expression )

Converts radians to degrees. For example:

  SELECT DEGREES(PI())  -> 180 

DIFFERENCE( character_expression , character_expression )

Compares how two arguments sound and returns a number from 0 to 4. A higher result indicates a better phonetic match. For example:

  SELECT DIFFERENCE('moe', 'low')  -> 3 

FILE_ID( file_name )

Returns the file ID for the logical file _ name . For example:

  SELECT FILE_ID('master')  -> 1 

FILE_NAME( file_id )

Returns the logical filename for the file _ id . For example:

  SELECT FILE_NAME(1)  -> 'master' 

FILEGROUP_ID( filegroup_name )

Returns filegroup ID for the logical filegroup_name . For example:

  SELECT FILEGROUP_ID('PRIMARY')  -> 1 

FILEGROUP_NAME( filegroup_id )

Returns the logical filegroup name for filegroup_id . For example:

  SELECT FILEGROUP_NAME(1)  -> 'PRIMARY' 

FILEGROUPPROPERTY( filegroup_name , property )

Returns the filegroup property value for the specified property . For example:

  SELECT FILEGROUPPROPERTY('PRIMARY', 'IsReadOnly')  -> 0 

FILEPROPERTY( file , property )

Returns the file property value for the specified property . For example:

  SELECT FILEPROPERTY('pubs', 'SpaceUsed')  -> 160 

FULLTEXTCATALOGPROPERTY( catalog_name , property )

Returns the fulltext catalog properties. For example:

  SELECT FULLTEXTCATALOGPROPERTY('Cat_Desc', 'LogSize')  

FULLTEXTSERVICEPROPERTY( property )

Returns the fulltext service level properties. For example:

  SELECT FULLTEXTSERVICEPROPERTY('IsFulltextInstalled')  -> 1 

FORMATMESSAGE( msg_number , param_value [,... n ] )

Constructs a message from an existing message in the SYSMESSAGES table. (Similar to RAISEERROR ). For example:

  sp_addmessage 50001, 1, 'Table %s has %s rows.'   SELECT  FORMATMESSAGE(50001, 'AUTHORS',   (SELECT COUNT(*) FROM AUTHORS))  'Table AUTHORS has 23.' 

FREETEXT ( { column * }, freetext_string )

Used for a full-text search. Returns rows with column that match the meaning, but not exactly the value, of freetext_string .

FREETEXTTABLE( table , { column * }, freetext_string [, top_n_by_rank ] )

Used for a full-text search. Returns rows from table with column that match the meaning, but not exactly the value, of freetext_string . For example:

  SELECT * from FREETEXTTABLE (authors, *, 'kev')  

GETANSINULL( [ database ] )

Returns default nullability setting for new columns. For example:

  SELECT GETANSINULL()  -> 1 

GETDATE()

Returns current date and time. For example:

  SELECT GETDATE()  -> 2003-06-27 19:26:59.893 

GETUTCDATE()

Returns the current date as a Coordinated Universal Time (UTC) date. For example:

  SELECT GETUTCDATE()  -> 2003-06-28 02:26:46.720 

GROUPING( column_name )

Returns 1 when a row is added by CUBE or ROLLUP ; otherwise returns 0. For example:

  SELECT royalty, SUM(advance) 'total advance',   GROUPING(royalty) 'grp'   FROM titles   GROUP BY royalty WITH ROLLUP  royalty        total advance            grp  ---------      ---------------------    --- NULL           NULL                     0   10             57000.0000               0   12             2275.0000                0   14             4000.0000                0   16             7000.0000                0   24             25125.0000               0   NULL           95400.0000               1 

HOST_ID()

Returns the workstation ID. For example:

  SELECT HOST_ID()  -> 216 

HOST_NAME()

Returns the process host name. For example:

  SELECT HOST_NAME()  -> 'PLATO' 

IDENT_CURRENT( table_name )

Returns the last identity value generated for the specified table. For example:

  SELECT IDENT_CURRENT('jobs')  -> 876 

IDENT_INCR( table_or_view )

Returns an identity column increment value. For example:

  SELECT IDENT_INCR('jobs')  -> 1 

IDENT_SEED( table_or_view )

Returns an identity seed value. For example:

  SELECT IDENT_SEED('jobs')  -> 1 

IDENTITY( data_type [, seed , increment ]) AS column_name

Used in a SELECT INTO statement to insert an identity column into the destination table. For example:

  SELECT IDENTITY(int, 1,1) AS ID   INTO NewTable   FROM OldTable  

INDEX_COL( table , index_id , key_id )

Returns an index column name given a table name, index ID, and the sequential number of the column in the index key. For example:

  SELECT INDEX_COL(OBJECT_ID('authors'), 1, 1)  -> NULL 

INDEXPROPERTY( table_id , index , property )

Returns an index property (such as FILLFACTOR ). For example:

  SELECT INDEXPROPERTY(OBJECT_ID('authors'),   'UPKCL_auidind', 'IsPadIndex')  

ISDATE( expression )

Validates if a character string can be converted to DATETIME . For example:

  SELECT ISDATE(NULL), ISDATE(GETDATE())  0      1 

IS_MEMBER( { group role } )

Returns true or false (1 or 0) depending on whether the user is a member of the specified Windows NT group or SQL Server role . For example:

  SELECT IS_MEMBER('db_owner')  -> 0 

IS_SRVROLEMEMBER( role [, login ] )

Returns true or false (1 or 0) depending on whether the user is a member of the specified server role . For example:

  SELECT IS_SRVROLEMEMBER('sysadmin')  -> 0 

ISNULL( check_expression , replacement_value )

Returns the first argument if it is not NULL; otherwise returns the second argument. For example:

  SELECT ISNULL(NULL, 'NULL')  -> 'NULL' 

ISNUMERIC( expression )

Validates if a character string can be converted to NUMERIC . For example:

  SELECT ISNUMERIC('3.1415'), ISNUMERIC('IRK')  1     0 

LEFT( character_expression , integer_expression )

Returns the leftmost integer_expression characters of character_expression . For example:

  SELECT LEFT('Wet Paint', 3)  -> 'Wet' 

LEN( string_expression )

Returns the number of characters in the expression. For example:

  SELECT LEN('Wet Paint')  -> 9 

LOG( float_expression )

Returns the natural logarithm. For example:

  SELECT LOG(PI())  -> 1.1447298858494002 

LOG10( float_expression )

Returns the base-10 logarithm. For example:

  SELECT LOG10(PI())  -> 0.49714987269413385 

LTRIM( character_expression )

Trims leading space characters. For example:

  SELECT LTRIM('   beaucoup   ')  -> 'beaucoup   ' 

MONTH( date )

Returns the month part of the date provided. For example:

  SELECT MONTH(GETDATE())  -> 6 

NCHAR( integer_expression )

Returns the UNICODE character with the given integer code. For example:

  SELECT NCHAR(120)  -> 'x' 

NEWID()

Creates a new unique identifier of type UNIQUEIDENTIFIER . For example:

  SELECT NEWID()  -> '32B35185-F55E-4FE0-B2C8-B57B35815C12' 

NULLIF( expression , expression )

Returns a NULL if the two specified expressions are equivalent. For example:

  SELECT NULLIF(5, 5)  -> NULL 

OBJECT_ID( object )

Returns the object ID of object . For example:

  SELECT OBJECT_NAME (OBJECT_ID('authors'))  -> 'authors' 

OBJECT_NAME( object_id )

Returns the object name of an object with the object ID. For example:

  SELECT OBJECT_NAME (OBJECT_ID('authors'))  -> 'authors' 

OBJECTPROPERTY( id , property )

Returns the properties of objects in the current database. For example:

  SELECT OBJECTPROPERTY (object_id('authors'),'ISTABLE')  -> 1 

OPEN {[ GLOBAL ] cursor_name } cursor_variable_name }

Opens a local or global cursor.

OPENDATASOURCE( provider_name , init_string )

Makes a connection to a data source without using a linked server name. For examples, refer to the "Loaders" section of the SQL Server User's Guide.

OPENQUERY( linked_server , query )

Queries a remote data source previously configured as a linked server. For an example, refer to the "Loaders" section of the SQL Server User's Guide.

OPENROWSET( provider_name , { datasource ; user_id , password provider_string }, {[ catalog .][ schema .] object query } )

Queries a remote data source without setting it up as a linked server.

PARSENAME( object_name , object_piece )

Returns the database name, owner name, server name, or object name for the object specified. Object_piece is an integer between 1 and 4. For example:

  SELECT PARSENAME('pubs..authors', 1)  -> 'authors'  SELECT PARSENAME('pubs..authors', 2)  -> NULL  SELECT PARSENAME('pubs..authors', 3)  -> 'pubs'  SELECT PARSENAME('pubs..authors', 4)  -> NULL 

PATINDEX( '%pattern%' , expression )

Returns the position of the first occurrence of a pattern in a string. For example:

  SELECT PATINDEX('%Du%', 'Donald Duck')  -> 8 

PERMISSIONS( [ object_id [, column ] ] )

Returns a numeric value representing a bitmap with the current user's permissions on the specified object or column. For example:

  SELECT PERMISSIONS(OBJECT_ID('authors'))&8  -> 8 

PI()

Returns the pi constant. For example:

  SELECT 2*PI()  -> 6.2831853071795862 

RADIANS( numeric_expression )

Converts degrees to radians. For example:

  SELECT RADIANS(90.0)  -> 1.570796326794896600 

RAND( [ seed ] )

Returns a pseudo-random FLOAT type value between and 1. For example:

  SELECT RAND(PI())  -> 0.71362925915543995 

REPLICATE( character_expression , integer_expression )

Repeats a string a number of times. For example:

  SELECT REPLICATE('FOOBAR', 3)  -> 'FOOBARFOOBARFOOBAR' 

REPLACE( string_expression1 , string_expression2 , string_expression3 )

Performs a search-and-replace on string_expression1 , replacing each occurrence of string_expression2 with string_expression3 . For example:

  SELECT REPLACE('Donald Duck', 'Duck', 'Trump')  -> 'Donald Trump' 

REVERSE( character_expression )

Reverses the characters of a string. For example:

  SELECT REVERSE('Donald Duck')  -> 'kcuD dlanoD' 

RIGHT( character_expression , integer_expression )

Returns the rightmost integer_expression characters of character_expression . For example:

  SELECT RIGHT('Donald Duck', 4)  -> 'Duck' 

ROUND ( number , decimal [, function ])

Returns number rounded to decimal places right of the decimal point. Note that decimal , an integer, can be negative to round off digits left of the decimal point. If a nonzero integer is provided for function , the return value will be truncated; otherwise the value is rounded. For example:

  SELECT ROUND(PI(), 2)  -> 3.1400000000000001 

ROWCOUNT_BIG()

Returns the number of rows affected by the most recent query. (Same as @@ROWCOUNT, but returns a BIGINT type.) For example:

  SELECT ROWCOUNT_BIG()  -> 1 

RTRIM( character_expression )

Trims trailing space characters from the expression. For example:

  SELECT RTRIM('   beaucoup   ')  -> '   beaucoup' 

SIGN( numeric_expression )

Returns -1 if the argument is negative, 0 if it is zero, and 1 if the argument is positive. For example:

  SELECT SIGN(-PI())  -> -1.0 

SIN( number )

Returns the sine of number , where number is in radians. For example:

  SELECT SIN(0)  -> 0.000000 

SOUNDEX( character_expression )

Returns a four-character code based on how the argument string sounds. For example:

  SELECT SOUNDEX('char')  -> 'C600' 

SPACE( integer_expression )

Returns a string consisting of a given number of space characters. For example:

  SELECT SPACE(5)  -> '     ' 

STATS_DATE( table_id , index_id )

Returns the date and time that index statistics were last updated. For example:

  SELECT i.name, STATS_DATE(i.id, i.indid)   FROM sysobjects o, sysindexes i   WHERE o.name = 'authors' AND o.id = i.id  UPKCL_auidind   2000-08-06 01:34:00.153         aunmind         2000-08-06 01:34:00.170 

STDEV( expression )

Returns the standard deviation of values in expression . For example:

  SELECT STDEV(qty) FROM sales  -> 16.409201831957116 

STDEVP( expression )

Returns the standard deviation for the population of values in expression . For example:

  SELECT STDEVP(qty) FROM sales  -> 16.013741264834152 

STR( number [, length [, decimal ]] )

Converts number to a character string with length length and decimal decimal places.

STUFF( string1 , start , length , string2 )

Replaces the length characters at position start within string1 with those in string2 . For example:

  SELECT STUFF('Donald Duck', 8, 4, 'Trump')  -> 'Donald Trump' 

SUBSTRING( string , start , length )

Extracts length characters from string starting at the character in the s tart position. For example:

  SELECT SUBSTRING('Donald Duck', 8, 4)  -> 'Duck' 

SUSER_ID( [ login ] )

Returns the system user ID of a given login name. Incidentally, this function will always return NULL with SQL Server 2000 or later. Therefore, avoid using this function.

SUSER_SID( [ login ] )

Returns the Security ID (SID) for the current user, or for the specified login . The SID is retuned in binary format. For example:

  SELECT SUSER_SID('montoyai')  0x68FC17A71010DE40B005BCF2E443B377 

SUSER_SNAME( [ server_user_sid ] )

Returns the login name for the current user, or for the specified login Security ID (SID). For example:

  SELECT SUSER_SNAME()  -> 'montoyai' 

TAN( number )

Returns the tangent of number , where number is in radians. For example:

  SELECT TAN(3.1415)  -> -0.000093 

TEXTPTR( column )

Returns a pointer to a TEXT , NTEXT , or IMAGE column in VARBINARY format. For example:

  SELECT TEXTPTR(pr_info)   FROM pub_info WHERE pub_id = '0736'   ORDER BY pub_id  0xFEFF6F00000000005C00000001000100 

TEXTVALID( table.column , text_ptr )

Returns true or false (1 or 0), depending on whether the provided pointer to a TEXT , NTEXT , or IMAGE column is valid. For example:

  SELECT pub_id, 'Valid (if 1) Text data'   = TEXTVALID ('pub_info.logo', TEXTPTR(logo))   FROM pub_info   ORDER BY pub_id  0736        1 0877        1 1389        1 1622        1 1756        1 9901        1 9952        1 9999        1 

TYPEPROPERTY( datatype , property )

Returns information about datatype properties. The datatype argument can contain the name of any datatype, and property can be a string containing one of the following:

Precision

The precision of the datatype is the number of digits or characters that it can store.

Scale

The scale is the number of decimal places for a numeric datatype. A NULL value will be returned if datatype is not a numeric datatype. For example:

  SELECT TYPEPROPERTY('decimal', 'PRECISION')  -> 38 

UNICODE( ncharacter_expression )

Returns the UNICODE code point for the first character of the input parameter. For example:

  SELECT UNICODE('Hello!')  -> 72 

USER_ID( [ user ] )

Returns the user ID for user in the current database. If user is omitted, then the current user's ID will be returned. For example:

  SELECT USER_ID()  -> 2 

USER_NAME( [ id ] )

Returns the current username in the current database. For example:

  SELECT USER_NAME()  -> 'montoyai' 

VAR( expression )

Returns the statistical variance for the values represented by expression . For example:

  SELECT VAR(qty) FROM sales  -> 269.26190476190476 

VARP( expression )

Returns statistical variance for the population represented by all values of expression in a group. VARP is an aggregate function. For example:

  SELECT VARP(qty) FROM sales  -> 256.43990929705217 

YEAR( date )

Returns an integer that is the YEAR part of the specified date. For example:

  SELECT YEAR(CURRENT_TIMESTAMP)  -> 2003