Single-row functions.

NULL value functions.

Any arithmetic operation on a NULL value results in a NULL. The same model is true for most
functions as well. Only functions
NVL
NVL2
CONCAT
DECODE
DUMP
and REPLACE
can return non-NULL value when called with a NULL argument.
NVL function
The NVL function takes two arguments,
NVL(x, y)
where both x and y are expressions. This function returns y, if x is NULL. If x is not NULL, it
returns x.
NVL2 function
The function NVL2 is very similar to the NVL function. NVL2 takes three arguments
NVL2(x, y, z)
where x, y, and z are expressions. This function returns z if x is NULL, and y if x is not NULL.
This function allows us to implement an IF...THEN...ELSE construct based on the nullity
of data.










Character functions.
Function Description Example
ASCII(s) Returns the ASCII code of the
first character of the string s.
ASCII('test') = 116
CHR(i) Returns the character with the
ASCII code i.
CHR(116) = 't'
CONCAT(s1, s2) Returns string s2 appended
to s1.
CONCAT('Wel', 'come') =
'Welcome'
INITCAP(s) Returns the string s with the
first character of each word in
uppercase and all others in
lowercase.
INITCAP('hello SIR') =
'Hello Sir'
INSTR(s, sub[, pos[, n]
])
Returns the numeric character
position in s where the n-th
occurrence of sub is found. The
search begins at the
position pos in s. If the
substring not found, it returns
0. If the posis negative, the
search is performed backwards
(from right to left). The default
values for pos and n are 1.
INSTR('test', 't', 2)
= 4
LENGTH(s) Returns the number of
characters in the string s.
LENGTH('Welcome Sir')
= 11
LOWER(s) Returns the string s with all
characters in lowercase.
LOWER('Welcome Sir') =
'welcome sir'
LPAD(s, i[, s1]) Returns the string s expanded
in length to i characters,
using s1 to fill in space as
needed on the left side.
LPAD('test', 11, '*-')
= '*-*-*-*test'
LTRIM(s, s1) Returns s without any leading
character that appear in s1. If
no s1 character are leading
LTRIM('welcome',
'slow') = 'elcome'
characters in s, then s is
returned unchanged.
RPAD(s, i[, s1]) Returns the string s expanded
in length to i characters,
using s1 to fill in space as
needed on the right side.
LPAD('test', 11, '*-')
= 'test*-*-*-*'
RTRIM(s, s1) Returns s without any trailing
character that appear in s1. If
no s1 character are trailing
characters in s, then s is
returned unchanged.
RTRIM('Mississippi',
'pi') = 'Mississ'
REPLACE(s, s1[, s2]) Returns s with all occurrences
of substring s1 replaced
with s2. By default s2 is NULL
and all occurrences of s1 are
removed.
REPLACE('www.yahoo.com
', 'yahoo', 'google')
= 'www.google.com'
SUBSTR(s, pos[, len]) Returns the portion of the
string s that is len characters
long, beginning at position pos.
If pos is negative, the position
is counted backwards from the
end of the string. The function
returns NULL if len is less or
equal to zero. If len is skipped,
it returns the remaining of s.
SUBSTR('welcome', 4) =
'come'
SOUNDEX(s) Returns the soundex phonetic
representation of the string s.
It's useful when we need to find
words that sound like a given
one.
SOUNDEX('John') = J500
= SOUNDEX('Jon')
TRANSLATE(s, s1, s2) Returns the string s with all
occurrences of characters
in s1 replaced with the
positionally corresponding
characters is s2.If s2 is shorter
than s1, the unmatched
characters in s1are removed.
TRANSLATE('alfabet',
'abscde', 'BCDE') =
'BlfBCt'
TRIM([s1 kw FROM] s) This function returns the
string s with all s1 (leading,
trailing, or both) occurrences of
characters in s removed. If
present kw is one of the
following LEADING, TRAILI
NG, or BOTH (by default
it's BOTH). The default value
of s1 is a space character.
TRIM(BOTH '.' FROM
'etc ...') = 'etc '
UPPER(s) Returns the string s with all
characters in uppercase.
UPPER('Welcome Sir') =
'WELCOME SIR'
Numeric functions.
Function Description
ABS(d) Returns the absolute value of the double d.
ACOS(d) Returns the arc cosine of the value d expressed in radians (d should be
between -1 and 1).
ASIN(d) Returns the arc sine of the value d expressed in radians (d should be between
-1 and 1).
ATAN(d) Returns the arc tangent of the value d expressed in radians.
ATAN2(d1, d2) Returns the arc tangent of the value d1/d2 expressed in radians.
BITAND(i1, i2) Returns the bitwise AND operation performed on two integer
arguments i1 and i2. The result is also an integer.
CEIL(d) Returns the smallest integer that is greater or equal to d.
COS(d) Evaluates the cosine of d radians.
COSH(d) Returns the hyperbolic cosine of d.
EXP(d) Returns e
d

FLOOR(d) Returns the largest integer less or equal to d.
LN(d) Returns the natural logarithm of d.
LOG(b, d) Returns log
b
d.
MOD(i1, i2) Returns i1 modulo i2, or the reminder of i1 divided i2.
POWER(d, p) Returns d to the pth power (d
p
).
ROUND(d, i) Returns d rounded to i digits of precision to the right of the decimal point.
If i is negative, d is rounded to the left of the decimal point.
SIGN(d) Returns -1 if d is negative, 1 if d is positive, and 0 if d is zero.
SIN(d) Evaluates the sine of d radians.
SINH(d) Returns the hyperbolic sine of d.
SQRT(d) Returns the square root of d.
TAN(d) Evaluates the tangent of d radians.
TANH(d) Returns the hyperbolic tangent of d.
TRUNC(d, i) Returns d truncated to i digits of precision to the right of the decimal point.
If i is negative, d is truncated to the left of the decimal point.
Date functions.
Implicit Date-to-String conversion is based on the value of the NLS_DATE_FORMAT variable.
To change its value use the ALTER SESSION command:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
Function Description Example
ADD_MONTHS(dt, i) Returns the date dt plus i months.
If i is a decimal number. Oracle
will automatically convert it to an
integer by truncating the decimal
ADD_MONTHS(SYSDATE, 2) =
'01-SEP-2003 12:00:03'
portion (it may also be negative).
CURRENT_DATE Returns the current date in the
Gregorian calendar for the
session's time zone. It requires no
arguments.
CURRENT_DATE = '01-JUL-
2003 12:00:35'
CURRENT_TIMESTAM
P([p])
Returns the current date and time
in the session's time zone
to p digits of the
precision, p should be an integer 0
through 9 and defaults to 6. The
return datatype is TIMESTAMP
WITH TIME ZONE. (See
also LOCALTIMESTAMP)
CURRENT_TIMESTAMP(4) =
'01-JUL-03 12:00:51.8261
PM -04:00'
DBTIMEZONE Returns the database's time zone,
as set by the latest CREATE
DATABASE or ALTER
DATABASE SET
TIME_ZONE statement. The time
zone is a string specifying the
hours and minutes offset from
UTC (Coordinated Universal
Timezone, aka as GMT, or
Greenwich Mean Time) or a time
zone regional name. The valid
time zone regional name can be
found in the TZNAME column of
the view V$TIMEZONE_NAMES
DBTIMEZONE = '-07:00'
EXTRACT(c FROM dt
)
Returns the component specified
by c from the date/time or
interval dt. The valid components
are:

TIMEZONE_HO
UR

TIMEZONE_MI
NUTE


YEAR

MONT
H


MINU
TE

SECO
ND
EXTRACT(MONTH FROM
SYSDATE) = 7
TIMEZONE_RE
GION

TIMEZONE_AB
BR
DAY

HOUR

FROM_TZ(ts, tz) Returns a timestamp with time
zone for the timestamp ts using
the time zone value tz. The
string tz specifies the hours and
minutes offset from UTC or is a
time zone region name.
FROM_TZ(LOCALTIMESTAMP,
'+3:00') = '01-JUL-03
12:01:57.679850 PM
+03:00'
LAST_DAY(dt) Returns the last day of the month
for the date dt.
LAST_DAY('25-Feb-2004')
= '29-FEB-2004 00:00:00'
LOCALTIMESTAMP([
p])
Returns the current date and time
in the session's time zone
to p digits of the
precision, p should be an integer 0
through 9 and defaults to 6. This
function is similar to
theCURRENT_TIMESTAMP,
but the return datatype is
TIMESTAMP.
LOCALTIMESTAMP(2) = '01-
JUL-03 12:02:48.59 PM'
MONTHS_BETWEEN(d
t1, dt2)
Returns the number of months
that dt1 is later than dt2. A whole
number is returned
if dt1 and dt2 are the same day of
the month or if both are th last
day of the month.
MONTHS_BETWEEN('01-JAN-
2003', SYSDATE) = -6
NEW_TIME(dt, tz1,
tz2)
Returns the date in time
zone tz2 for the date dt in time
zone tz1. See time zone constants
NEW_TIME(SYSDATE, 'EST',
'PDT') = '01-JUL-2003
10:04:47'
NEXT_DAY(dt, s) Returns the date that corresponds
to the next day of week specified
by the string s following the
date dt. The time portion of the
date is the same as the time
portion of dt. The string s should
NEXT_DAY(SYSDATE, 'Mon')
= '07-JUL-2003 12:05:13'
be a text string containing the full
or abbreviated day of the week in
the session's language.
ROUND(dt[, fmt]) Returns the date dt rounded to the
granularity specified in the date-
format string fmt. See date format
codes.
ROUND(SYSDATE, 'HH24') =
'01-JUL-2003 11:00:00'
SESSIONTIMEZONE Returns the database's time zone
as per the last LATER
SESSION statement. It takes no
arguments.
SESSIONTIMEZONE = '-
04:00'
SYS_EXTRACT_UTC(
ts)
Returns the UTC (GMT) time
zone from the timestamp ts.
CURRENT_TIMESTAMP = '01-
JUL-03 11.48.19.618428
AM -04:00'
SYS_EXTRACT_UTC(CURRENT_
TIMESTAMP) = '01-JUL-03
03:47:05.917078 PM'
SYSDATE Returns the current date/time,
takes no arguments.
SYSDATE = '01-JUL-2003
11:50:08'
SYSTIMESTAMP Takes no arguments and returns a
TIMESTAMP WITH TIME
ZONE for the current database
date and time. The fractional
second is returned with six digits
of precision.
SYSTIMESTAMP = '01-JUL-
03 11:52:10.051237 AM -
04:00'
TRUNC(dt[, fmt]) Returns the date dt truncated to
the granularity specified by the
format string fmt.
TRUNC(SYSdate, 'DD') =
'01-JUL-2003 00:00:00'
TZ_OFFSET(tz) Returns the numeric time zone
offset for the textual time zone
name tz.
TZ_OFFSET('US/Eastern')
= '-04:00'
Conversion functions.
Function Description
ASCIISTR(s) Returns the ASCII equivalent of all characters in the
string s.
BIN_TO_NUM(bit_list) Takes a single argument bit_list - the comma-delimited list
of bits, and returns the numeric representation of all the bit-
field set bit_list.
CAST(expr AS type) Converts the expression expr into datatype type.
The expr can be an expression, subquery, or MULTISET
clause. See the possible conversion table for standard
datatypes.
CHARTOROWID(s) Returns the string s as a ROWID datatype.
COMPOSE(s) Returns the string s as a Unicode string in its fully
normalized form, in the same characters set as s.
CONVERT(s, dset[, sset]) Returns the string s converted from the soursecharacter
set sset to the destination character set dset.
DECOMPOSE(s) Returns the string s as a Unicode string after canonical
decomposition in the same character set as s.
HEXTORAW(x) Returns the hexadecimal string x converted to a RAW
datatype.
NUMTODSINTERVAL(x, st) Converts the number x into an INTERVAL DAY TO
SECOND datatype accordingly to the string st denoting the
units for x. Valid units are: 'DAY', 'HOUR', 'MINUTE',
and 'SECOND'.
NUMTOYMINTERVAL(x, st) Converts the number x into an INTERVAL YEAR TO
MONTH datatype accordingly to the string st denoting the
units for x. Valid units are: 'YEAR' and 'MONTH'.
RAWTOHEX(rs) Returns the raw string rs converted to hexadecimal.
ROWIDTOCHAR(x) Returns the string x (in the format of ROWID) converted
from ROWID.
TO_CHAR(x[, fmt[, nls]]) Takes up to three arguments, where x is either a date or a
number, fmt is a format string specifying the format
that x will appear in, and nls specifies language or location
formatting string.
If x is a date, fmt is a date format code.
If x is a number, fmt is a numeric format code.
TO_DATE(s[, fmt[, nls]]) Converts string s to DATE datatype accordingly the format
string fmt. The fmt string uses the same date format code.
The default is the value stored in
the NLS_DATE_FORMAT session variable.
TO_DSINTERVAL(s[, nls]) Returns the string s converted into an INTERVAL DAY
TO SECOND datatype.
TO_NUMBER(s[, fmt[, nls]]) Returns the numeric value represented by the string s. The
format string fmt specifies the format the that s appears in.
TO_YMINTERVAL(s)
Returns the string s converted into an INTERVAL YEAR
TO MONTH datatype.
UNISTR(s) Returns the string s in Unicode in the database Unicode
character set.
Other functions.
Function Description
BFILENAME(dir, file) Takes two arguments, where dir is a string
containing a directory name and file is a string
containing a file name. Returns an empty BFILE
locator. When used, the BFILE is instantiated.
Neither dir nor file needs to exist at the time the
function is called, but both must exist when the
locator is used.
COALESCE(expr_list) Returns the first non-NULL value in
the expr_list. If all expressions are NULL, then
NULL is returned. Each expression in
the expr_list should be the same type.
DECODE(x, v1, r1[, v2,r2 ...][, d]) The function compares the value of the
expression x with values of the
arguments v1, v2, etc. If the value of x is
equivalent to v1, then r1 is returned; otherwise
the additionalmatching expressions are
compared. If no match is found and the default
expression d is included, then the d is returned.
If no match is found and there is no d argument,
then NULL is returned.
Example:
SELECT sid, serial#, username,
DECODE(command, 0,
'None',
2,
'Instert',
3,
'Select',
6,
'Update',
7,
'Delete',
8,
'Drop',
'Other')
cmd
FROM v$session
WHERE type <> 'BACKGROUND';
DUMP(x[, fmt[, sb[,len]]]) Takes up to four arguments, where x is an
expression, fmt is a format specification for
octal (1008),
decimal (1010),
hexadecimal (1016),
or single character (1017)
, sb is the starting byte offset within x, and len is
the length in bytes to dump. The function
returns a character string containing the datatype
of x in numeric notation and the internal
presentation of x.
EMPTY_BLOB Returns an empty BLOB locator. This function
is used to initialize a BLOB variable or BLOB
column in a table.
EMPTY_CLOB Returns an empty CLOB locator. This function
is used to initialize a CLOB variable or CLOB
column in a table.
GREATEST(expr_list) Returns the expression from the expr_list that
sorts the highest in the data type of the first
expression. If the expression list contains a
NULL, then a NULL is returned.
LEAST(expr_list) Returns the expression from the expr_list that
sorts the lowest in the data type of the first
expression. If the expression list contains a
NULL, then a NULL is returned.
NULLIF(x1, x2) Returns NULL if the expression x1 is equal
to x2; otherwise returns x1.
SYS_CONNECT_BY_PATH(col,chr) This function works only with hierarchical
queries - queries that use CONNECT BY clause.
It returns the path of column col, delimited by
the character chr, from root to node for each
row returned by the CONNECT BY condition.
Example:
SELECT dep_id, dep_name,

SYS_CONNECT_BY_PATH(dep_name, '-
') 'Dep. Chain'
FROM departments
START WITH dep_id = 0
CONNECT BY PRIOR dep_id =
sup_dep_id;
SYS_CONTEXT(ns, par[,len]) Takes up to three arguments, where ns is a
namespace, par is a parameter associated with
the namespace ns, and len is the length of the
return value in bytes (default 256). The built-in
namespace USERENV contains information on
the current session. See the table
with parameters in the USERENV
UID Returns the integer user ID for the current user.
USER Returns a character string containing the
username for the current user.
USERENV(opt) Takes a single argument, where opt is one of the
following options:
ISDBA
SESSIONID
ENTRYID
INSTANCE
LANGUAGE
LANG
TERMINAL
This function is deprecated in release Oracle9i.
VSIZE(x) Returns the size in bytes of the internal
representation of the expression x.