Oracle function list in alphabetical order

NameDescription
ABS(x)absolute value of x
ACOS(n)Arccosine
ADD_MONTHS(d, n)Date d plus n months
ASCIIGives the ASCII value of the first character of a string.
ASCIISTRConverts x to an ASCII string, where x may be a string in any character set.
ASIN(n)Arcsine of n
ATAN(n)Arctangent of n
ATAN2(x,y)Returns the arctangent of x and y.
AVGCalculates the average
BIN_TO_NUMConverts a binary number x to a NUMBER.
BITAND(x,y)a bitwise AND on x and y.
CAST(x AS type)Converts x to a compatible type specified in type.
CEILReturns the ceiling value (next highest integer above a number).
CHARTOROWID(x)Converts x to a ROWID.
CHR(n)Character with ASCII value n
COALESCE(a, b, ...)The first not NULL argument (and NULL if all arguments are NULL)
COMPOSE(x)Converts x to a Unicode string in normalized form.
CONCATConcatenates two strings
CONVERT(x,source_char_set,dest_char_set)Converts x from source_char_set to dest_char_set.
COS(n)Cosine of n (n expressed in radians)
COSH(n)Hyperbolic cosine of n
COUNT(x)Returns the number of rows returned by a query involving x
CUBEReturns rows containing a subtotal for all combinations of columns
CUME_DIST()Returns the position of a value relative to a group.
CURRENT_DATEreturns the current date in the local time zone in the database session
CURRENT_TIMESTAMPReturns a TIMESTAMP WITH TIME ZONE for the session
DBTIMEZONEReturns the time zone for the database
DECODEUse it as if then else statement
DECOMPOSE(x)Decomposes the string into the same character set as x and convert x to a Unicode string.
DENSE_RANKRank the rows and does not skip ties
EXPReturns e raised to a value.
EXTRACTExtracts and returns the year, month, day, hour, minute, second, or time zone from x.
EXTRACT(c FROM d)Extract date/time component c from expression d
FIRSTFirst gets the first values in an ordered group.
First_valueGets the first rows in a window.
FLOORReturns the floor value (next lowest integer below number).
FROM_TZ(x, time_zone)Merges xand time_zone into one value.
GREATEST(a, b, ...)Greatest value of the function arguments
GROUP_IDRemoves duplicate rows returned by a ROLLUP or CUBE.
GROUPINGDeals with NULL value for ROLLUP and CUBE
GROUPING_IDReturns the decimal equivalent of the GROUPING bit vector.
HEXTORAW(x)Converts the character x containing hexadecimal digits (base-16) to a binary number (RAW).
HypotheticalRank and Distribution Functions
INITCAPChanges the first letter of a string or series of words into uppercase.
INSTRReturns the beginning location of a pattern in a given string.
LAG
LASTgets the last values in an ordered group.
LAST_DAY(d)Last day of the month containing date d
Last_valueGets the last rows in a window.
LEAST(a, b, ...)Least value of the function arguments
LENGTHReturns the length of a string.
LN(n),LOG(m,n)Natural logarithm, and logarithm base m
LOCALTIMESTAMPReturns a TIMESTAMP for the session.
LOWERConverts every letter of a string to lowercase.
LPADAdds a specified set of characters to the left of the original string.
LTRIMRemoves a set of characters from the left of a string.
MAXReturns the maximum value
MEDIANReturns the median value
MINReturns the minimum value
ModReturns The Remainder Of N/M Where Both N And M Are Integers.
MONTHS_BETWEEN(d, e)Months between dates d and e
NEW_TIME(x,time_zone1,time_zone2)Converts x from time_zone1 to time_zone2
NEW_TIME(d, z1, z2)Convert date/time d from time zone z1 to z2
NEXT_DAY(d, weekday)The first weekday (mon, tue, etc.) after d
NTILEGroups data by putting data into a number of percentile groups
NULLIF(a, b)NULL if a = b; otherwise a
NUMTODSINTERVAL(x)Converts the number x to an INTERVAL DAY TO SECOND.
NumToDSIntervalConverts number x to INTERVAL DAY TO SECOND.
NUMTOYMINTERVAL(x)Converts the number x to an INTERVAL YEAR TO MONTH.
NumToYMIntervalConverts number x to INTERVAL YEAR TO MONTH.
NVLReturns value if x is null; otherwise x is returned.
NVL2Returns value1 if x is not null; otherwise value2 is returned.
PARTITION BYSeparate data groups and perform calculation within that group
PERCENT_RANK()Returns the percent rank of a value relative to a group.
PERCENTILE_CONTChecks the percent rank values in each group
PERCENTILE_DISCChecks the cumulative distribution values
POWERReturns value raised to some exponential power.
RANKReturns the rank of items in a group and leaves a gap for a tie.
RATIO_TO_REPORT()Ratio to report
RAWTOHEX(x)Converts the binary number (RAW) x to a VARCHAR2 string in equivalent hexadecimal number.
RAWTONHEX(x)Converts the binary number (RAW) x to an NVARCHAR2 string in the equivalent hexadecimal number.
REGEXP_COUNTReturns the number of times the pattern is found.
REGEXP_INSTRReturns the location of a pattern in a given string and supports the regular expression.
REGEXP_LIKESearches x for the regular expression defined in the pattern parameter.
REGEXP_REPLACEReplaces string with the regular expression matching supports
REGEXP_SUBSTRReturns the substring with the regular expression matching supports
REGR_AVGX(y, x)Returns the average of x after eliminating x and y pairs
REGR_AVGY(y, x)Returns the average of y after eliminating x and y pairs
REGR_COUNT(y, x)Returns the number of non-null number pairs that are used to fit the regression line
REGR_INTERCEPT(y, x)Returns the intercept on the y-axis of the regression line
REGR_R2(y, x)Returns the coefficient of determination of the regression line
REGR_SLOPE(y, x)Returns the slope of the regression line
REGR_SXX(y, x)Returns REG_COUNT (y, x) * VAR_POP(x)
REGR_SXY(y, x)Returns REG_COUNT (y, x) * COVAR_POP(y, x)
REGR_SYY(y, x)Returns REG_COUNT (y, x) * VAR_POP (y)
REMAINDER(n1, n2)Identifies the multiple of n2 that is nearest to n1, and returns the difference between those two values.
REPLACEDoes the string replacement
ROLLUPReturns a row containing a subtotal
ROUND(d[, fmt])d rounded on fmt (the default for fmt is midnight)
ROUNDReturns the number rounded to nearest value, adjusts precision.
ROW_NUMBERrow number for ranking
ROWIDTOCHAR(x)Converts the ROWID x to a VARCHAR2 string.
ROWIDTONCHAR(x)Converts the ROWID x to an NVARCHAR2 string.
ROWNUMpseudo-column and is calculated as rows are retrieved
RPADAdds a specified set of characters to the right of the original string.
RTRIMRemoves a set of characters from the right of a string.
SESSIONTIMEZONEReturns the database session time zone
SignReturns 1 If The Argument Is Positive; -1 If The Argument Is Negative; And 0 If The Argument Is Negative.
SIN(n)Sine of n (n expressed in radians)
SINH(n)Hyperbolic sine of n
SOUNDEXConverts a string to a code value based on similar sounds.
Sqrt(x)Square Root Of x
STDDEVReturns the standard deviation
String||StringConcatenates two strings.
SUBSTRRetrieves a portion.
SUMReturns the sum
SYS_EXTRACT_UTC(x)Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP in UTC.
SYS_EXTRACT_UTCExtracts the UTC from a datetime value.
SysdateReturns the current operating system date from database server
SYSTIMESTAMPReturns a TIMESTAMP WITH TIME ZONE for the database.
TAN(n)Tangent of n (n expressed in radians)
TANH(n)Hyperbolic tangent of n
TO_BINARY_DOUBLE(x)Converts x to a BINARY_DOUBLE.
TO_BINARY_FLOAT(x)Converts x to a BINARY_FLOAT.
TO_BLOB(x)Converts x to a binary large object (BLOB).
TO_CHARConverts number and date value to string and format
TO_CLOB(x)Converts x to a character large object (CLOB). A CLOB is used to store large amounts of character data.
TO_DATEConverts string to date value
TO_DSINTERVAL(x)Converts the string x to an INTERVAL DAY TO SECOND.
TO_DSIntervalConverts string x to INTERVAL DAY TO SECOND.
TO_MULTI_BYTE(x)Converts the single-byte characters in x to the corresponding multi-byte characters.
TO_NCHAR(x)Converts x in the database character set to an NVARCHAR2 string.
TO_NCLOB(x)Converts x to a large object NCLOB.
TO_NUMBER(x [, format]Converts x to a NUMBER.
TO_SINGLE_BYTE(x)Converts the multi-byte characters in x to the corresponding single-byte characters.
TO_TIMESTAMP(x)Converts the string x to a TIMESTAMP.
TO_TIMESTAMP(x, [format])Converts the string x to a TIMESTAMP.
TO_TIMESTAMP_TZ(x)Converts the string xto a TIMESTAMP WITH TIME ZONE.
TO_TIMESTAMP_TZ(x, [format])Converts the string x to a TIMESTAMP WITH TIMEZONE.
TO_YMINTERVAL(x)Converts the string x to an INTERVAL YEAR TO MONTH.
TO_YMIntervalConverts string x to INTERVAL YEAR TO MONTH.
TRANSLATE(x,from_string, to_string)Converts all occurrences of from_string in x to to_string.
TRANSLATEReplaces a string character by character.
TRIMRemoves a set of characters from both sides.
TRUNC(d[, fmt])d truncated on fmt (the default for fmt is midnight)
TRUNCReturns the truncated value (removes decimal part of a number, precision adjustable).
TZ_OFFSET(time_zone)Returns the offset for time_zone in hours and minutes
UNISTR(x)Converts the characters in x to an NCHAR character.
UPPERConverts every letter to uppercase.
VARIANCEReturns the variance
VSIZEReturns the storage size.
Home »
Oracle »
Appendix » 

Related: