Built-in Functions in SQL

Numberic Functions

Function Input Argument Value Returned
ABS ( m ) m = value Absolute value of m
MOD ( m, n ) m = value, n = divisor Remainder of m divided by n
POWER ( m, n ) m = value, n = exponent m raised to the nth power
ROUND ( m [, n ] ) m = value, n = number of decimal places, default 0 m rounded to the nth decimal place
TRUNC ( m [, n ] ) m = value, n = number of decimal places, default 0 m truncated to the nth decimal place
SIN ( n ) n = angle expressed in radians sine (n)
COS ( n ) n = angle expressed in radians cosine (n)
TAN ( n ) n = angle expressed in radians tan (n)
ASIN ( n ) n is in the range -1 to +1 arc sine of n in the range -π/2 to +π/2
ACOS ( n ) n is in the range -1 to +1 arc cosine of n in the range 0 to π
ATAN ( n ) n is unbounded arc tangent of n in the range -π/2 to + π/2
SINH ( n ) n = value hyperbolic sine of n
COSH ( n ) n = value hyperbolic cosine of n
TANH ( n ) n = value hyperbolic tangent of n
SQRT ( n ) n = value positive square root of n
EXP ( n ) n = value e raised to the power n
LN ( n ) n > 0 natural logarithm of n
LOG ( n2, n1 ) base n2 any positive value other than 0 or 1, n1 any positive value logarithm of n1, base n2
CEIL ( n ) n = value smallest integer greater than or equal to n
FLOOR ( n ) n = value greatest integer smaller than or equal to n
SIGN ( n ) n = value -1 if n < 0, 0 if n = 0, and 1 if n > 0

Here are some examples of the use of some of these numeric functions: 

select round (83.28749, 2) from dual;

select sqrt (3.67) from dual;

select power (2.512, 5) from dual;

String Functions

Function Input Argument Value Returned
INITCAP ( s ) s = character string First letter of each word is changed to uppercase and all other letters are in lower case.
LOWER ( s ) s = character string All letters are changed to lowercase.
UPPER ( s ) s = character string All letters are changed to uppercase.
CONCAT ( s1, s2 ) s1 and s2 are character strings Concatenation of s1 and s2. Equivalent to s1 || s2
LPAD ( s1, n [, s2] ) s1 and s2 are character strings and n is an integer value Returns s1 right justified and padded left with n characters from s2; s2 defaults to space.
RPAD ( s1, n [, s2] ) s1 and s2 are character strings and n is an integer value Returns s1 left justified and padded right with n characters from s2; s2 defaults to space.
LTRIM ( s [, set ] ) s is a character string and set is a set of characters Returns s with characters removed up to the first character not in set; defaults to space
RTRIM ( s [, set ] ) s is a character string and set is a set of characters Returns s with final characters removed after the last character not in set; defaults to space
REPLACE ( s, search_s [, replace_s ] ) s = character string, search_s = target string, replace_s = replacement string Returns s with every occurrence of search_s in s replaced by replace_s; default removes search_s
SUBSTR ( s, m [, n ] ) s = character string, m = beginning position, n = number of characters Returns a substring from s, beginning in position m and n characters long; default returns to end of s.
LENGTH ( s ) s = character string Returns the number of characters in s.
INSTR ( s1, s2 [, m [, n ] ] ) s1 and s2 are character strings, m = beginning position, n = occurrence of s2 in s1 Returns the position of the nth occurrence of s2 in s1, beginning at position m, both m and n default to 1.

Here are some examples of the use of String functions: 


select concat ('Alan', 'Turing') as "NAME" from dual;

select 'Alan' || 'Turing' as "NAME" from dual;

select initcap ("now is the time for all good men to come to the aid of the
party") as "SLOGAN" from dual;

select substr ('Alan Turing', 1, 4) as "FIRST" from dual;

String / Number Conversion Functions

Function Input Argument Value Returned
NANVL ( n2, n1 ) n1, n2 = value if (n2 = NaN) returns n1 else returns n2
TO_CHAR ( m [, fmt ] ) m = numeric value, fmt = format Number m converted to character string as specified by the format
TO_NUMBER ( s [, fmt ] ) s = character string, fmt = format Character string s converted to a number as specified by the format

Formats for TO_CHAR Function

Symbol Explanation
9 Each 9 represents one digit in the result
0 Represents a leading zero to be displayed
$ Floating dollar sign printed to the left of number
L Any local floating currency symbol
. Prints the decimal point
, Prints the comma to represent thousands

Group Functions

Function Input Argument Value Returned
AVG ( [ DISTINCT | ALL ] col ) col = column name The average value of that column
COUNT ( * ) none Number of rows returned including duplicates and NULLs
COUNT ( [ DISTINCT | ALL ] col ) col = column name Number of rows where the value of the column is not NULL
MAX ( [ DISTINCT | ALL ] col ) col = column name Maximum value in the column
MIN ( [ DISTINCT | ALL ] col ) col = column name Minimum value in the column
SUM ( [ DISTINCT | ALL ] col ) col = column name Sum of the values in the column
CORR ( e1, e2 ) e1 and e2 are column names Correlation coefficient between the two columns after eliminating nulls
MEDIAN ( col ) col = column name Middle value in the sorted column, interpolating if necessary
STDDEV ( [ DISTINCT | ALL ] col ) col = column name Standard deviation of the column ignoring NULL values
VARIANCE ( [ DISTINCT | ALL ] col ) col = column name Variance of the column ignoring NULL values

Date and Time Functions

Function Input Argument Value Returned
ADD_MONTHS ( d, n ) d = date, n = number of months Date d plus n months
LAST_DAY ( d ) d = date Date of the last day of the month containing d
MONTHS_BETWEEN ( d, e ) d and e are dates Number of months by which e precedes d
NEW_TIME ( d, a, b ) d = date, a = time zone (char), b = time zone (char) The date and time in time zone b when date d is for time zone a
NEXT_DAY ( d, day ) d = date, day = day of the week Date of the first day of the week after d
SYSDATE none Current date and time
GREATEST ( d1, d2, ..., dn ) d1 ... dn = list of dates Latest of the given dates
LEAST ( d1, d2, ..., dn ) d1 ... dn = list of dates Earliest of the given dates

Date Conversion Functions

Function Input Argument Value Returned
TO_CHAR ( d [, fmt ] ) d = date value, fmt = format for string The date d converted to a string in the given format
TO_DATE ( s [, fmt ] ) s = character string, fmt = format for date String s converted to a date value
ROUND ( d [, fmt ] ) d = date value, fmt = format for string Date d rounded as specified by the format
TRUNC ( d [, fmt ] ) d = date value, fmt = format for string Date d truncated as specified by the format

Date Formats

Format Code Description Range of Values
DD Day of the month 1 - 31
DY Name of the day in 3 uppercase letters SUN, ..., SAT
DAY Complete name of the day in uppercase, padded to 9 characters SUNDAY, ..., SATURDAY
MM Number of the month 1 - 12
MON Name of the month in 3 uppercase letters JAN, ..., DEC
MONTH Name of the month in uppercase padded to a length of 9 characters JANUARY, ..., DECEMBER
RM Roman numeral for the month I, ..., XII
YY or YYYY Two or four digit year 71 or 1971
HH:MI:SS Hours : Minutes : Seconds 10:28:53
HH 12 or HH 24 Hour displayed in 12 or 24 hour format 1 - 12 or 1 - 24
MI Minutes of the hour 0 - 59
SS Seconds of the minute 0 - 59
AM or PM Meridian indicator AM or PM
SP A suffix that forces the number to be spelled out. e.g. TWO THOUSAND NINE
TH A suffix meaning that the ordinal number is to be added e.g. 1st, 2nd, 3rd, ...
FM Prefix to DAY or MONTH or YEAR to suppress padding e.g. MONDAY with no extra spaces at the end

Here are some examples of the use of the Date functions: 

select to_char ( sysdate, 'MON DD, YYYY' ) from dual;

select to_char ( sysdate, 'HH12:MI:SS AM' ) from dual;

select to_char ( new_time ( sysdate, 'CDT', 'GMT'), 'HH24:MI' ) from dual;

select greatest ( to_date ( 'JAN 19, 2000', 'MON DD, YYYY' ),
                  to_date ( 'SEP 27, 1999', 'MON DD, YYYY' ),
		  to_date ( '13-Mar-2009', 'DD-Mon-YYYY' ) )
from dual;

select next_day ( sysdate, 'FRIDAY' ) from dual;

select last_day ( add_months ( sysdate, 1 ) ) from dual;