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 BLOBs 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 4th. 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 (1st,
2nd, 3rd, ...)
- %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( ) -> 'login@machine.com'
SYSTEM_USER( ) -> 'login@machine.com'
SESSION_USER( ) -> 'login@machine.com'
- 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 -> '\00C4BC'
- 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 := [ASC|DESC] [NULLS {FIRST|LAST}]
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 := [ASC|DESC] [NULLS {FIRST|LAST}]
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, '$999.99') FROM DUAL -> $123.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\00F1o') FROM DUAL -> 'El Niño'
- 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')
0
- 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
start 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
|