Oracle SQL-Supported Functions
|
Table 4-9: Oracle-Supported
Functions |
|
|
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 |
|
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 number ranging from -1 to
1. The resulting value ranges from - |
|
atan(number) |
Returns the arctangent of any number. The
resulting value ranges from - |
|
atan2(number,nbr) |
Returns the arctangent of number and nbr.
The values for number and nbr are not restricted, but the
results range from - |
|
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. |
|
chartorowid(char) |
Converts a value from a character datatype (CHAR or VARCHAR2 datatype) to 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). |
|
concat(string1, string2) |
Returns string1 concatenated with string2. It is equivalent to the concatenation operator (||). |
|
convert(char_value, target_char_set, source_char_set) |
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. |
|
covar_pop(expression1, expression2) over |
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). |
|
covar_samp(expression1, expression2) over(analytics) |
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 |
|
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 string1 that is the first character of this occurrence. |
|
instrb(string1, string2, [start_a[t, occurrence]]) |
The same as INSTR, except that start_at and the return value are expressed in bytes instead of characters. |
|
lag(expression [,offset][,default]) over(analytics) |
Provides access to more than one row of a table at the same time without a self join; refer to the vendor documentation for more information. |
|
last_day(date) |
Returns the date of the last day of the month that contains date. |
|
last_value(expression) over (analytics) |
Returns the last value in an ordered set of values; refer to the vendor documentation for more information. |
|
lead(expression [,offset][,default]) over(analytics) |
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 char in 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; string2 defaults to a single blank. |
|
ltrim(string[, set]) |
Removes all characters in set from the left of string. Set defaults 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 |
|
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 dividend divided by divider ; returns the dividend if divider is 0. |
|
months_between |
Returns 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. |
|
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:
|
|
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 [,'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 |
Divides an ordered data set into a number of buckets numbered 1 to expression and assigns the appropriate bucket number to each row. |
|
numtodsinterval |
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 |
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 |
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 |
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 row from a table or view. A special reference value is returned for the object instance that is bound to the variable or row. |
|
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:
These can be used as aggregate or analytic functions. |
|
replace(string, search_string [,replacement_string]) |
Returns string with every occurrence of search_string replaced with replacement_string. |
|
round (number, decimal) |
Returns number rounded to decimal places right of the decimal point. When decimal is omitted, number is rounded to 0 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. |
|
row_number ( ) over |
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. |
|
rowidtochar(rowid) |
Converts a rowid 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 set removed; set defaults 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. |
|
substr(extraction_string [FROM starting_position] [FOR length]) |
Refer to the earlier section on SUBSTR. |
|
substrb(extraction_string [FROM starting_position] [FOR length]) |
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 |
Returns the value of attribute associated with the context namespace, usable in both SQL and PL/SQL statements. |
|
sys_guid( ) |
Generates and returns a globally unique identifier |
|
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 |
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_parameter option offers additional control over formatting options. |
|
to_char (number |
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_parameter option offers additional control over formatting options. |
|
to_date(string [, format |
Converts string (in CHAR or VARCHAR2) to a DATE datatype. The nls_parameter 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_parameter']]) |
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_parameter 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 the section "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. |
|
trim({[LEADING | TRAILING | BOTH] trim_char | trim_char } FROM trim_source} ) |
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 0 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. |
|
uid |
Returns an integer that uniquely identifies the session user who logged on. No parameters are needed. |
|
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) |
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:
|
|
vsize(expression) |
Returns the number of bytes in the internal representation of expression. When expression is null, it returns null. |
and
is expressed in radians.