The following section
provides a full listing and description of each vendor-supported
function. These functions are vendor-specific. Thus, a MySQL
function, for example, is not guaranteed to be supported by any other
vendor. MySQL functions are provided to give an idea of the
capabilities available within the various products. Refer to the
vendor's documentation for exact syntax usage.
Microsoft SQL Server-Supported Functions
Function
Description
abs(numeric_expression)
Returns absolute value.
acos( float_expression)
Returns angle (in radians) whose cosine is the specified argument.
app_name( )
Returns application name for current session; set by application.
ascii(character_expression)
Converts character to a numeric ASCII code.
asin( float_expression)
Returns angle (in radians) whose sine is the specified argument.
atan( float_expression)
Returns angle (in radians) whose tangent is the specified argument.
atn2( float_expression, float_expressioin)
Returns angle (in radians) whose tangent is argument1/argument1.
avg([ All| Distinct] Expression)
Computes average of a column.
binary_checksum(* | expression [,...n])
Returns binary checksum for list of expressions or row of a table.
cast(Expression as Data Type)
Converts a valid SQL Server expression to the specified datatype.
ceiling(numeric_expression)
Returns smallest integer greater than or equal to the argument.
Searches columns on exact or "fuzzy" matches of
contains_seach_criteria. It is an elaborate
function used to perform full-text searches. Refer to the vendor
documentation for more information.
Returns a table with exact and "fuzzy" matches of
contains_search_condition. It is an elaborate
function used to perform full-text searches. Refer to the vendor
documentation for more information.
Computes the number of rows, including those with NULL values.
count( DISTINCT expression )
Calculates the number of distinct non-NULL values in a column or expression. Each group of rows with the same value of expression adds 1 to the result.
count( expression )
Returns the number of rows with non-NULL values in a certain column
or expression.
count_big([All | Distinct] expression)
Same as count except returns big integer.
current_timestamp
Returns current date and time.
current_user
Returns username in the current database of the current session.
datalength(expression)
Returns number of bytes in a character or binary string.
databasepropertyex(database, property)
Returns database option or property.
dateadd(datepart, number, date)
Adds a number of dateparts (e.g., days) to a datetime value.
datediff(datepart, startdate, enddate)
Calculates difference between two datetime values expressed in
certain dateparts.
datename(datepart, date)
Returns name of a datepart (e.g., month) of a datetime argument.
datepart(datepart, date)
Returns value of a datepart (e.g., hour) of a datetime argument.
day(date)
Returns an integer value representing the day of the date provided as
a parameter.
Used for full-text search; returns a table with columns that match
the meaning but don't exactly match value of
freetext_string.
getdate( )
Returns current date and time.
getansinull([`database'])
Returns default nullability setting for new columns.
getutcdate( )
Returns Universal Time Coordinate (UTC) date.
grouping(column_name)
Returns 1 when the row is added by CUBE or ROLLUP; otherwise, returns
0.
host_id( )
Returns workstation ID of a given process.
host_name( )
Returns process hostname.
ident_incr(`table_or_view')
Returns identity-column increment value.
ident_seed(`table_or_view')
Returns identity seed value.
ident_current(`table_name')
Returns the last identity value generated for the specified table.
identity(data_type [, seed, increment]) As
column_name
Used in SELECT INTO statement to insert an
identity column into the destination table.
index_col(`table', index_id, key_id)
Returns index column name, given table ID, index ID, and column
sequential number in the index key.
indexproperty(table_id, index, property)
Returns index property (such as Fillfactor).
isdate(expression)
Validates if a character string can be converted to DATETIME.
is_member({`group' |
`role'})
Returns true or false (1 or 0) depending on whether user is a member
of NT group or SQL Server role.
is_srvrolemember(`role'
[,'login'])
Returns true or false (1 or 0) depending on whether user is a member
of specified server role.
isnull(check_expression, replacement_value)
Returns the first argument if it is not NULL; otherwise, returns the
second argument.
isnumeric(expression)
Validates if a character string can be converted to NUMERIC.
left(character_expression, integer_expression)
Returns a portion of a character expression, starting at
integer_expression from left.
len(string_expression)
Returns the number of characters in the expression.
log(float_expression)
Returns natural logarithm.
log10(float_expression)
Returns base-10 logarithm.
lower(character_expression)
Converts a string to lowercase.
ltrim(character_expression)
Trims leading-space characters.
max([All | Distinct] expression)
Finds maximum value in a column.
min([All | Distinct] expression)
Finds minimum value in a column.
month(date)
Returns month part of the date provided.
nchar(integer_expression)
Returns the unicode character with the given integer code.
newid( )
Creates a new unique identifier of type
uniqueidentifier.
nullif(expression, expression)
Returns NULL if two specified expressions are equivalent.
object_id(`object')
Returns object ID and given name.
object_name(object_id)
Returns object name and given ID.
objectproperty(id, property)
Returns properties of objects in the current database.
MySQL-Supported Functions
Table 4.8 provides an alphabetical listing of MySQL-supported functions.
Function
Description
abs(X)
Returns the absolute value of X.
acos(X)
Returns the arc cosine of X, i.e., the value
whose cosine is X; returns NULL if
X is not in the range -1 to 1.
ascii(str)
Returns the ASCII code value of the leftmost character of the string
str; returns
if str is the empty string; returns NULL if
str is NULL.
asin(X)
Returns the arc sine of X, i.e., the value whose
sine is X; returns NULL if
X is not in the range -1 to 1.
atan(X)
Returns the arctangent of X, i.e., the value
whose tangent is X.
atan2(X,Y)
Returns the arctangent of the two variables X
and Y.
avg(expr)
Returns the average value of expr.
benchmark(count,expr)
Executes the expression expr
count times. It may be used to time how fast
MySQL processes the expression. The result value is always 0.
binary
Casts the string following it to a binary string.
bin(N)
Returns a string representation of the binary value of
N, where N is a long
(BIGINT ) number.
bit_count(N)
Returns the number of bits that are set in the argument
N.
bit_and(expr)
Returns the bitwise AND of all bits in
expr. The calculation is performed with 64-bit
(BIGINT ) precision.
bit_or(expr)
Returns the bitwise OR of all bits in
expr. The calculation is performed with 64-bit
(BIGINT ) precision.
CASE value WHEN [compare-value] THEN result [WHEN
[compare-value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN
result ...] [ELSE result] END
The first version returns the result where
value=compare-value. The second version returns
the result for the first condition that is true.
If there is no matching result value, then the result after
ELSE is returned. If there is no
ELSE part, NULL is returned.
ceiling(X)
Returns the smallest integer value not less than
X.
char(N,...)
Interprets the arguments as integers and returns a string consisting
of the characters given by the ASCII code values of those integers.
NULL values are skipped.
coalesce(list)
Returns first non-NULL element in the list.
concat(str1,str2,...)
Returns the string that results from concatenating the arguments.
concat_ws(septor, str1, str2,...)
Stands for CONCAT With Septor and is a special form of
CONCAT( ). The first argument is the septor
for the rest of the arguments. The septor and the rest of the
arguments can be a string. If the septor is NULL, the result is
NULL. The function skips any NULLs and empty strings after the
septor argument. The septor is added between the strings to be
concatenated.
connection_id( )
Returns the connection ID (thread_id ) for the
connection. Every connection has its own unique ID.
conv(N,from_base,to_base)
Converts numbers between different number bases; returns a string
representation of the number N, converted from
base from_base to base
to_base ; returns NULL if any argument is NULL.
cos(X)
Returns the cosine of X, where
X is given in radians.
cot(X)
Returns the cotangent of X.
count(DISTINCT expr,[expr...])
Returns a count of the number of different values.
count(expr)
Returns a count of the number of non-NULL values in the rows
retrieved by a SELECT statement.
curdate( )
current_date
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.
curtime( )
current_time
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.
database( )
Returns the current database name.
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. ADDDATE(
) and SUBDATE( ) are synonyms for
DATE_ADD( ) and DATE_SUB( ).
date is a DATETIME or
DATE value specifying the starting date.
expr is an expression specifying the interval
value to be added or subtracted from the starting date.
expr may start with a - for negative intervals.
type indicates how the expression should be
interpreted.
date_ format(date, format)
Formats the date value according to the format string.
dayname(date)
Returns the name of the weekday for date.
dayofmonth(date)
Returns the day of the month for date, in the range 1 to 31.
dayofweek(date)
Returns the weekday index for date (1 = Sunday, 2 = Monday, . . . 7 =
Saturday).
dayofyear(date)
Returns the day of the year for date, in the range 1 to 366.
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( ).
degrees(X)
Returns the argument X, converted from radians
to degrees.
elt(N,str1,str2,str3,...)
Returns str1 if N = 1,
str2 if N = 2, and so on.
Returns NULL if N is less than 1 or greater than
the number of arguments. ELT( ) is the
complement of FIELD( ).
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.
encrypt(str[,salt])
Encrypts str using the Unix crypt(
) system call. The salt argument
should be a string with two characters.
exp(X)
Returns the value of e (the base of natural
logarithms) raised to the power of X.
export_set(bits,on,off,[septor,[number_of_bits]])
Returns a string where every bit set in `bit' gets an
`on' string and every reset bit gets an `off
' string. Each string is septed with `septor'
(default `,') and only `number_of_bits'
(default 64) of `bits' is used.
field(str,str1,str2,str3,...)
Returns the index of str in the
str1, str2,
str3, . . . list. Returns
if str is not found. FIELD(
) is the complement of ELT( ).
find_in_set(str,strlist)
Returns a value 1 to N if the string
str is in the list strlist
consisting of N substrings. A string list is a
string composed of substrings septed by `,'
characters. Returns
if str is not in strlist or
if strlist is the empty string. Returns NULL if
either argument is NULL. This function does not work properly if the
first argument contains a `,'.
floor(X)
Returns the largest integer value not greater than
X.
format(X,D)
Formats the number X to a format like
`#,###,###.##', rounded to D
decimals. If D is 0, the result has no decimal
point or fractional part.
from_days(N)
Given a daynumber N, returns a
DATE value. Not intended for use with values
that precede the advent of the Gregorian calendar (1582), due to the
days lost when the calendar was changed.
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.
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.
get_lock(str,timeout)
Tries to obtain a lock with a name given by the string
str, with a timeout of
timeout seconds. Returns 1 if the lock is
obtained successfully,
if the attempt times out, or NULL if an error occurs.
greatest(X,Y,...)
Returns the largest (maximum-valued) argument.
hex(N)
Returns a string representation of the hexadecimal value of
N, where N is a long
(BIGINT ) number. This is equivalent to
CONV(N,10,16). Returns NULL if
N is NULL.
interval(N,N1,N2,N3,...)
Returns 0 if N < N1, 1 if
N < N2, and so on. All
arguments are treated as integers. It is required that
N1 < N2 <
N3 < . . . < Nn for
this function to work correctly.
hour(time)
Returns the hour for time, in the range 0 to 23.
if(expr1,expr2,expr3)
If expr1 is TRUE (expr1
<>
and expr1 <> NULL), then IF(
) returns expr2, else it returns
expr3. IF( ) returns a
numeric or string value, depending on the context in which it is
used.
ifnull(expr1,expr2)
If expr1 is not NULL, IFNULL(
) returns expr1; otherwise it returns
expr2. IFNULL( ) returns a
numeric or string value, depending on the context in which it is
used.
isnull(expr)
If expr is NULL, ISNULL( )
returns 1; otherwise it returns 0.
insert(str,pos,len,newstr)
Returns the string str. The substring begins at
position pos and is 10 characters long, replaced
by the string newstr.
instr(str,substr)
Returns the position of the first occurrence of substring
substr in string str.
last_insert_id([expr])
Returns the last automatically generated value that was inserted into
an AUTO_INCREMENT column.
lcase(str)
lower(str)
Returns the string str with all characters
changed to lowercase according to the current character-set mapping
(default is ISO-8859-1 Latin1).
least(X,Y,...)
With two or more arguments, returns the smallest (minimum-valued)
argument.
left(str,len)
Returns the leftmost len characters from the
string str.
length(str)
octet_length(str)
char_length(str)
character_length(str)
These functions return the length of the string
str.
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 the file privilege.
locate(substr,str)
position(substr IN str)
Returns the position of the first occurrence of substring
substr in string str.
Returns
if substr is not in str.
locate(substr,str,pos)
Returns the position of the first occurrence of substring
substr in string str,
starting at position pos; returns
if substr is not in str.
log(X)
Returns the natural logarithm of X.
log10(X)
Returns the base-10 logarithm of X.
lpad(str,len,padstr)
Returns the string str, left-padded with the
string padstr until str is
10 characters long.
ltrim(str)
Returns the string str with leading-space
characters removed.
make_set(bits,str1,str2, . . . )
Returns a set (a string containing substrings septed by
`,' characters) consisting of the strings that have the
corresponding bits in bit set. str1 corresponds
to bit 0, str2 to bit 1, etc. NULL strings in
str1, str2, . . . are not appended to the
result.
md5(string)
Calculates a MD5 checksum for the string. Value
is returned as a 32-long hex number.
min(expr)
max(expr)
Returns the minimum or maximum value of expr.
MIN( ) and MAX( ) may take
a string argument; in such cases they return the minimum or maximum
string value.
minute(time)
Returns the minute for time, in the range 0 to 59.
mod(N,M)
% Modulo (like the % operator in C); returns the remainder of
N divided by M.
month(date)
Returns the month for date, in the range 1 to 12.
monthname(date)
Returns the name of the month for date.
now( )
sysdate( )
current_timestamp
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.
nullif(expr1,expr2)
If expr1 = expr2 is true, returns NULL;
otherwise returns expr1.
oct(N)
Returns a string representation of the octal value of
N, where N is a long
number. This is equivalent to CONV(N,10,8).
Returns NULL if N is NULL.
ord(str)
If the leftmost character of the string str is a
multibyte character, returns the code of multibyte character by
returning the ASCII code value of the character in the format of:
If the leftmost character is not a multibyte character, returns the
same value as the ASCII( ) function does.
password(str)
Calculates a password string from the plain-text password
str. This is the function that is used for
encrypting MySQL passwords for storage in the Password column of the user grant table.
period_add(P,N)
Adds N months to period P
(in the format YYMM or YYYYMM). Returns a value in the format YYYYMM.
Note that the period argument P is not a date
value.
period_diff(P1,P2)
Returns the number of months between periods P1
and P2. P1 and
P2 should be in the format YYMM or YYYYMM. Note
that the period arguments P1 and
P2 are not date values.
pi( )
Returns the value of -π.
pow(X,Y)
power(X,Y)
Returns the value of X raised to the power of
Y.
quarter(date)
Returns the quarter of the year for date, in the range 1 to 4.
radians(X)
Returns the argument X, converted from degrees
to radians.
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.
release_lock(str)
Releases the lock named by the string str that
was obtained with GET_LOCK( ). Returns 1 if the
lock is released,
if the lock isn't locked by this thread (in which case the lock
is not released), and NULL if the named lock doesn't exist.
repeat(str,count)
Returns a string consisting of the string str
repeated count times. If
count <= 0, returns an empty string. Returns
NULL if str or count are
NULL.
replace(str, from_str,to_str)
Returns the string str with all occurrences of
the string from_str replaced by the string
to_str.
reverse(str)
Returns the string str with the order of the
characters reversed.
right(str,ten)
Returns the rightmost 10 characters from the string
str.
round(X)
Returns the argument X, rounded to an integer.
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.
rpad(str,len,padstr)
Returns the string str, right-padded with the
string padstr until str is
ten characters long.
rtrim(str)
Returns the string str with trailing space
characters removed.
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.
second(time)
Returns the second for time, in the range 0 to 59.
sign(X)
Returns the sign of the argument as -1, 0, or 1, depending on whether
X is negative, zero, or positive.
sin(X)
Returns the sine of X, where
X is given in radians.
soundex(str)
Returns a soundex string from
str. Two strings that sound "about the
same" should have identical soundex
strings.
A "standard" soundex string is four
characters long, but the SOUNDEX( ) function
returns an arbitrarily long string. A SUBSTRING(
) can be used on the result to get a
"standard" soundex string. All
non-alphanumeric characters are ignored in the given string. All
international alphabetic characters outside the A-Z range are treated
as vowels.
space(N)
Returns a string consisting of N space
characters.
sqrt(X)
Returns the nonnegative square root of X.
std(expr)
stddev(expr)
Returns the standard deviation of expr. The
STDDEV( ) form of this function is provided for
Oracle compatability.
strcmp(expr1,expr2)
STRCMP( ) returns
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.
substring(str,pos,len)
substring(str FROM pos FOR len)
mid(str,pos,len)
Returns a substring 10 characters long from string
str, starting at position
pos. The variant form that uses
FROM is ANSI SQL92 syntax.
substring_index(str,delim,count)
Returns the substring from string str after
count occurrences of the delimiter
delim. If count is
positive, everything to the left of the final delimiter (counting
from the left) is returned. If count is
negative, everything to the right of the final delimiter (counting
from the right) is returned.
substring(str,pos)
substring(str FROM pos)
Returns a substring from string str starting at
position pos.
sum(expr)
Returns the sum of expr. Note that if the return
set has no rows, it returns NULL.
tan(X).
Returns the tangent of X, where
X is given in radians.
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.
time_to_sec(time)
Returns the time argument, converted to seconds.
to_days(date)
Given a date, returns a daynumber (the number of days since year 0).
trim([[BOTH | LEADING | TRAILING] [remstr] FROM]
str)
Returns the string str with all
remstr prefixes and/or suffixes removed. If none
of the specifiers BOTH,
LEADING, or TRAILING are
given, BOTH is assumed. If
remstr is not specified, spaces are removed.
truncate(X,D)
Returns the number X, truncated to
D decimals. If D is 0, the
result has no decimal point or fractional part.
ucase(str)
upper(str)
Returns the string str with all characters
changed to uppercase according to the current character set mapping
(default is ISO-8859-1 Latin1).
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.
user( )
system_user( )
session_user( )
These functions return the current MySQL username.
version( )
Returns a string indicating the MySQL server version.
week(date)
week(date, first)
With a single argument, returns the week for date, in the range
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).
weekday(date)
Returns the weekday index for date (0 = Monday, 1 = Tuesday, . . . 6
= Sunday).
year(date)
Returns the year for date, in the range 1000 to 9999.
yearweek(date)
yearweek(date, first)
Returns year and week for a 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.
Oracle SQL-Supported Functions
Table 4.9 provides an alphabetical listing of the SQL functions specific to Oracle.
Function
Description
abs(number)
Returns the absolute value of number.
acos(number)
Returns the arc cosine of number ranging from -1
to 1. The result ranges from 0 to π and is expressed in radians.
add_months(date, int)
Returns the date date plus
int months.
ascii(string)
Returns the decimal value in the database character set of the first
character of string; returns an ASCII value when
the database character set is 7-bit ASCII; returns EBCDIC values if
the database character set is EBCDIC Code Page 500.
asin(number)
Returns the arc sine of numberranging from -1 to 1. The resulting value ranges from -π/2 to π/2 and is expressed in radians.
atan(number)
Returns the arctangent of any number. The resulting value ranges from -π/2 to π/2 and is expressed in radians.
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.
avg([DISTINCT ] expression) over (analytics)
Returns the average value of expr. It can be
used as an aggregate or analytic function (analytic functions are
beyond the scope of this text).
bfilename(`directory','filename')
Returns a BFILE locator associated with a
physical LOB binary filename on the
server's filesystem in directory.
ceil(number)
Returns smallest integer greater than or equal to
number.
chartotrid(char)
Converts a value from a character datatype (CHAR
or VARCHAR2 datatype) to
trID 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).
concat(string1, string2)
Returns string1concatenated with string2. It is
equivalent to the concatenation operator (||).
Converts a character string from one character set to another;
returns the char_value in the
target_char_set after converting
char_value from the
source_char_set.
corr(expression1, expression2) over (analytics)
Returns the correlation coefficient of a set of numbered pairs
(expressions 1 and 2). It can be used as an
aggregate or analytic function (analytic functions are beyond the
scope of this text).
cos(number)
Returns the cosine of number as an angle
expressed in radians.
cosh(number)
Returns the hyperbolic cosine of number.
count
Returns the number of rows in the query; refer to the earlier section
on COUNT for more information.
Returns the population covariance of a set of number pairs
(expressions 1 and 2). It can be used as an
aggregate or analytic function (analytic functions are beyond the
scope of this text).
Returns the sample covariance of a set of number pairs
(expressions 1 and 2). It can be used as an
aggregate or analytic function (analytic functions are beyond the
scope of this text).
cume_dist( ) ( [OVER (query)] ORDER BY...)
The cumulative distribution function computes the relative position
of a specified value in a group of values.
decode(expr search , result [,. n] [,default])
Compares expr to the search value; if
expr is equal to a search, returns the result.
Without a match, DECODE returns default, or
NULL if default is omitted. Refer to Oracle
documentation for more details.
dense_rank( ) ( [OVER (query)] ORDER BY...)
Computes the rank of each row returned from a query with respect to
the other rows, based on the values of the
value_exprs in the
ORDER_BY_clause.
deref(expression)
Returns the object reference of 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.
empth[B | C]lob( )
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.
exp(number)
Returns E raised to the number
ed power, where E = 2.71828183.
first_value( expression) over (analytics)
Returns the first value in an ordered set of values.
floor(number)
Returns largest integer equal to or less than
number.
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.
grouping(expression)
Distinguishes null cause by a super-aggregation in GROUP
BY extension from an actual null value.
hextoraw(string)
Converts string containing
hexadecimal digits into a raw value.
initcap(string)
Returns string, with the first letter of each
word in uppercase and all other letters in lowercase.
instr(string1, string2, start_at, occurrence)
Searches one character string for another character string.
INSRT search
char1 with a starting
position of start_at (an integer) looking for
the numeric occurrence within
string2. Returns the
position of the character in string1that is the first character of this occurrence.
Provides access to more than one row of a table at the same time
without a self join. Analytic functions are beyond the scope of this
text.
least(expression [,...n])
Returns the least of the list of expressions.
length(string)
Returns the integer length of string, or null if
string is null.
lengthb(string)
Returns the length of charin bytes; otherwise, the same as
LENGTH.
ln(number)
Returns the natural logarithm of number, where
the number is greater than
0.
log(base_number, number)
Returns the logarithm of any base_number of
number.
lower(string)
Returns string in the same datatype as it was
supplied with all characters lowercase.
lpad(string1, number [,string2])
Returns string1, left-padded to length
number using characters in
string2; string2defaults to a single blank.
ltrim(string[, set])
Removes all characters in set from the left of
string. Setdefaults to a single blank.
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.
max([DISTINCT] expression) over (analytics)
Returns maximum value of expression. It can be
used as an aggregate or analytic function (analytic functions are
beyond the scope of this text).
min([DISTINCT] expression) over (analytics)
Returns minimum value of expression. It can be
used as an aggregate or analytic function (analytic functions are
beyond the scope of this text).
mod(dividend, divider)
Returns remainder of dividenddivided by divider ; returns the
dividend if
divider is 0.
months_between(date1, date2)
Returns number of months between dates
date1 and
date2. When date1is later than date2, the result is
positive. If it is earlier, the result is negative.
new_time(date, time_zone1, time_zone2)
Returns the date and time in
time_zone2 when date and
time in time_zone1 are
date. Time_zones 1 and 2
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 Time 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.
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 [,'nlspmeter'])
Returns string with the first letter of each
word in uppercase and all other letters in lowercase. The
nlspmeter offers special linguistic sorting
features.
nls_lower(string, [,'nlspmeter'])
Returns string with all letters lowercase. The
nlspmeter offers special linguistic sorting
features.
nlssort(string [,'nlspmeter'])
Returns the string of bytes used to sort string.
The nlspmeter offers special linguistic
sorting features.
nls_upper string [,'nlspmeter'])
Returns string with all letters uppercase. The
nlspmeter offers special linguistic sorting
features.
ntile(expression) over ( query_partition ORDER
BY...)
Divides an ordered data set into a number of buckets numbered 1 to
expression and assigns the
appropriate bucket number to each tr.
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.
numtoyminterval(number, `string')
Converts number to an
INTERVAL DAY TO MONTH literal, where
number is a number or an
expression resolving to a number, such as a numeric datatype column.
nvl(expression1, expression2)
If expression1 is null,
expression2 is returned in the place of a null
value. Otherwise, expression1 is returned. The
expressions may be any datatype.
nvl2(expression1, expression2, expression3)
Similar to NLV, 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.
percent_rank( ) over ( query_partition ORDER
BY...)
Similar to the CUME_DIST analytical function.
Rather than return the cumulative distribution, it returns the
percentage rank of a row compared to the others in its result set.
Refer to the vendor documentation for more assistance.
power(number, power)
Returns number raised to the nth
power. The base and the exponent can be any
numbers, but if number is
negative, power must be an
integer.
rank (value_expression) over ( query_partition ORDER BY
...)
Computes the rank of each row returned from a query with respect to
the other rows returned by the query, based on the values of the
value_expression in the
ORDER_BY_clause.
ratio_to_report (value_exprs) over (
query_partition)
Computes the ratio of a value to the sum of a set of values. If
values_expr is null, the
ratio-to-report value also is null.
rawtohex(raw)
Converts a raw value to a
string (character datatype) of its hexadecimal equivalent.
ref(table_alias)
REF takes a table alias associated with a tr
from a table or view. A special reference value is returned for the
object instance that is bound to the variable or tr.
reftohex(expression)
Converts argument expression to a character
value containing its hexadecimal equivalent.
regr_ xxx(expression1, expression2) over
(analytics)
Linear regression functions fit an ordinary-least-squares regression
line to a set of number pairs where expression1
is the dependent variable and expression2 is the
independent variable. The linear regression functions are:
REGR_SLOPE: returns the slope of the line
REGR_INTERCEPT: returns the y-intercept of the regression line
REGR_COUNT: returns the number of non-null pairs fitting the
regression line
REGR_R2: returns the coefficient of determination for the regression
REGR_AVGX: returns the average of the independent variable
REGR_AVGY: returns the average of the dependent variable
Returns string with every
occurrence of search_stringreplaced with replacement_string.
round (number, decimal)
Returns number rounded to
decimal places right of the
decimal point. When decimalis omitted, numberis rounded to
places. Note that decimal, an integer, can be
negative to round off digits left of the decimal point.
round (date[, format])
Returns the date rounded to
the unit specified by the format model format.
When format is omitted,
date is rounded to the
nearest day.
tr_number ( ) over ( query_partition ORDER BY ...
)
Assigns a unique number to each row where it is applied in the
ordered sequence of rows specified by the
ORDER_BY_clause, beginning with 1.
tridtochar(trid)
Converts a trid value to
VARCHAR2 datatype, 18 characters long.
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.
rtrim(string[,set])
Returns string, with all the rightmost
characters that appear in setremoved; setdefaults to a single blank.
sign(number)
When number < 0, returns -1. When
number = 0, returns 0. When
number > 0, returns 1.
sin(number)
Returns the sine of number as an angle expressed
in radians.
sinh(number)
Returns the hyperbolic sine of 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.
sqrt(number)
Returns square root of number, a nonnegative
number.
stddev( [DISTINCT] expression) over (analytics)
Returns sample standard deviation of a set of numbers shown
as expression.
stdev_pop(expression) over (analytics)
Computes the population standard deviation and returns the square
root of the population variance.
seddev_samp(expression) over (analytics)
Computes the cumulative sample standard deviation and returns the
square root of the sample variance.
SUBSTRB is the same as
SUBSTR, except that the arguments
m
starting_position and
length are expressed in
bytes, rather than in characters.
sum([DISTINCT ] expression) over (analytics)
Returns sum of values of expr ; refer to vendor
documentation for assistance with analytics and the
OVER subclause.
sys_context(`namespace','attribute'[,length])
Returns the value of attributeassociated with the context
namespace, usable in both SQL and PL/SQL
statements.
sys_guid( )
Generates and returns a globally unique identifier
(RAW value) made up of 16
bytes.
sysdate
Returns the current date and time, requiring no arguments.
tan(number)
Returns the tangent of number as an angle
expressed in radians.
tanh(number)
Returns the hyperbolic tangent of number
to_char (date [, format [,
`nls_pmeter']])
Converts date to a
VARCHAR2 in the format specified by the date
format format. When fmt is
omitted, date is converted
to the default date format. The nls_pmeter
option offers additional control over formatting options.
to_char (number [, format [,
`nls_pmeter']])
Converts number to a
VARCHAR2 in the format specified by the number
format format. When fmt is
omitted, number is
converted to a string long enough to hold the
number. The nls_pmeter
option offers additional control over formatting options.
to_date(string [, format [,
`nls_pmeter']])
Converts string (in
CHAR or VARCHAR2) to a
DATE datatype. The
nls_pmeter option offers additional control
over formatting options.
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 multi-byte characters.
to_number(string [, format
[,'nls_pmeter']])
Converts a numeric string (of
CHAR or VARCHAR2 datatype)
to a value of a NUMBER datatype in the format specified by the
optional format model format. The
nls_pmeter option offers additional control
over formatting options.
to_single_byte(string)
Returns string with all of
its multi-byte characters converted to their corresponding
single-byte characters.
translate(`char_value',
`from_text', `to_text')
Returns char_value with all
occurrences of each character in
from_text replaced by its
corresponding character in to_text; refer to CONVERT and TRANSLATE earlier in this
chapter for more information on TRANSLATE.
translate (text USING [CHAR_CS | NCHAR_CS] )
Converts text into the
character set specified for conversions between the database
character set or the national character set.
Enables leading or trailing characters (or both) to be trimmed from a
character string.
trunc (base [, number])
Returns base truncated to
number decimal places. When
number is omitted,
base is truncated to
places. Number can be
negative to truncate (make zero)
number digits left of the
decimal point.
trunc (date [, format])
Returns date with any time data truncated to the
unit specified by format. When
format is omitted,
date is truncated to the
nearest whole day.
upper(string)
Returns string with all letters in uppercase.
user
Returns the name of the session user who logged on in
VARCHAR2.
userenv(option)
Returns information about the current session in
VARCHAR2.
value(table_alias)
Takes as a table alias associated with a row in an object table and
returns object instances stored within the object table.
var_pop(expression) over (analytics)
Returns the population variance of a set of numbers after discarding
the nulls in the expression number
set. Analytic functions are covered in the vendor
documentation.
var_samp(expression) over (analytics)
Returns the sample variance of a set of numbers after discarding the
nulls in the expression number
set. Analytic functions are covered in the vendor
documentation.
variance([DISTINCT] expression) over (analytics)
Returns 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
vsize(expression)
Returns the number of bytes in the internal representation of
expression. When expression
is null, it returns null.
PostgreSQL-Supported Functions
Table 4.10 lists the functions specific to PostgreSQL.
Function
Description
abstime(timestamp)
Converts to abstime
abs(float8)
Returns absolute value
acos(float8)
Returns arccosine
age(timestamp)
Preserves months and years
age(timestamp,
timestamp)
Preserves months and years
area(object)
Returns area of item
asin(float8)
Returns arcsine
atan(float8)
Returns arctangent
atan2(float8,float8)
Returns arctangent
box(box,box)
Returns intersection box
box(circle)
Converts circle to box
box(point,point)
Returns points to box
box(polygon)
Converts polygon to box
broadcast(cidr)
Constructs broadcast address as text
broadcast(inet)
Constructs broadcast address as text
CASE WHEN expr THEN expr [...] ELSE expr END
Returns expression for first true WHEN clause
cbrt(float8)
Returns cube root
center(object)
Returns center of item
char(text)
Converts text to char type
char(varchar)
Converts varchar to char type
char_length(string)
Returns length of string
character_length(string)
Returns length of string
circle(box)
Converts to circle
circle(point,float8)
Converts point to circle
COALESCE(list)
Returns first non-NULL value in list
cos(float8)
Returns cosine
cot(float8)
Returns cotangent
date_part(text,timestamp)
Returns portion of date
date_part(text,interval)
Returns portion of time
date_trunc(text,timestamp)
Truncates date
degrees (float8)
Converts radians to degrees
diameter(circle)
Returns diameter of circle
exp(float8)
Raises e to the specified exponent
float(int)
Converts integer to floating point
float4(int)
Converts integer to floating point
height(box)
Returns vertical size of box
host(inet)
Extracts host address as text
initcap(text)
Converts first letter of each word to uppercase
interval(reltime)
Converts to interval
integer(float)
Converts floating point to integer
isclosed(path)
Returns a closed path
isopen(path)
Returns an open path
isfinite(timestamp)
Returns a finite time
isfinite(interval)
Returns a finite time
length(object)
Returns length of item
ln(float8)
Returns natural logarithm
log(float8)
Returns base-10 logarithm
lower(string)
Converts string to lowercase
lseg(box)
Converts box diagonal to lseg
lseg(point,point)
Converts points to lseg
lpad(text,int,text)
Returns left-pad string to specified length
ltrim(text,text)
Returns left-trim characters from text
masklen(cidr)
Calculates netmask length
masklen(inet)
Calculates netmask length
netmask(inet)
Constructs netmask as text
npoint(path)
Returns number of points
NULLIF(input,value)
Returns NULL if input = value, else returns input
octet_length(string)
Returns storage length of string
path(polygon)
Converts polygon to path
pclose(path)
Converts path to closed
pi( )
Returns fundamental constant
polygon(box)
Returns 12-point polygon
polygon(circle)
Returns 12-point polygon
polygon(npts,circle)
Returns npts polygon
polygon(path)
Converts path to polygon
point(circle)
Returns center
point(lseg,lseg)
Returns intersection
point(polygon)
Returns center
position(string in string)
Returns location of specified substring
pow (float8,float8)
Raises a number to the specified exponent
popen(path)
Converts path to open path
reltime(interval)
Converts to reltime
radians(float8)
Converts degrees to radians
radius(circle)
Returns radius of circle
round(float8)
Rounds to nearest integer
rpad(text,int,text)
Converts right pad string to specified length
rtrim(text,text)
Converts right trim characters from text
sin(float8)
Returns sine
sqrt(float8)
Returns square root
substring(string [from int] [for int])
Extracts specified substring
substr(text,int[,int])
Extracts specified substring
tan(float8)
Returns tangent
text(char)
Converts char to text type
text(varchar)
Converts varchar to text type
textpos(text,text)
Locates specified substring
timestamp(date)
Converts to timestamp
timestamp(date,time)
Converts to timestamp
to_char(timestamp, text)
Converts timestamp to string
to_char(int, text)
Converts int4/int8 to string
to_char(float, text)
Converts float4/float8 to string
to_char(numeric, text)
Converts numeric to string
to_date(text, text)
Converts string to date
to_number(text, text)
Converts string to numeric
to_timestamp(text, text)
Converts string to timestamp
translate(text,from,to)
Converts character in string
trim([leading|trailing|both] [string] from
string)