Built-in Functions in SQL

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 ('sagar', ' Sidana') 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 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;