Oracle Functions
Reference: SQL Manual 12C available from Moodle Chapter 7 pp 7-1 – 7-447 or online at: https://docs.oracle.com/database/121/SQLRF/toc.htm
Oracle functions are useful for manipulating data by decomposing data elements. They use numerical, date or string values and may appear in a SQL statement wherever a value or attribute is used. Functions are categorised according to their operand types:
¡ñ Arithmetic for manipulation of numerical data
¡ñ Text for manipulation of alphanumeric data
¡ñ Date for manipulation of date/time-related data
¡ñ General for manipulation of any data type
¡ñ Conversion for manipulation of data type conversions, and
¡ñ Group for manipulation sets of values
The examples below show the uses for some of the more common Oracle SQL function
Arithmetic Functions
abs(n)
ceil(n)
floor(n)
Largest integer equal to or less than n
select floor(10.6) from dual;
mod(m,n)
Remainder of m divided by n. If n=0, then m is returned
select mod(7,5) from dual;
power(m,n)
Number m raised to the power of n
select power(3,2) from dual;
round(n,m)
Results rounded to m places to the right of decimal point
select round(15.193,1) from dual;
sign(n)
If n=0, returns 0; if n>0, returns 1; if n<0, returns -1
select sign(12 - 45) from dual;
The column's absolute value
select abs(sallower - salupper) from salgrade;
Nearest whole integer greater than or equal to number
select ceil(10.6) from dual;
Page 1 of 5
sqrt(n)
trunc(n,m)
Truncates n to m decimal points, if m is omitted then n is truncated to 0 places
select trunc(15.79,1) from dual;
select trunc(15.79) from dual;
Text Functions
initcap(char)
Changes the first character of each character string to uppercase
select initcap('mr teplow') from dual;
lower(char), upper(char)
Makes the entire string lowercase/uppercase
select lower(ename) from employee;
replace(char, str1, str2)
Character string with every occurrence of str1 being replaced with str2
select replace('jack and jue','j','bl') from dual;
substr(char,m,n)
Picks off part of the character string char starting in position m for n characters
select substr('ABCDEF',2,1) from dual;
length(char)
Length of char
select length('Anderson') from dual;
str1 || str2
Concatenates two character fields together
select deptname || ', ' || deptlocation as "Department Name and
Location"
from department;
lpad(char,n,char2)/rpad(char,n,char2)
Pads char left/right to size n using char2
select lpad('Page 1', 15, '*') as "Lpad example"
from dual;
select rpad('Page 1', 15, '*') as "Rpad example"
from dual;
Square root of n
select sqrt(120) from dual;
select round(sqrt(120),2) from dual;
Page 2 of 5
ltrim(char[, k]), rtrim(char[, k])
remove characters from the left/right of char, until the first character not in k - if k is not specified blanks are trimmed
select ltrim('Intro to SQL', 'InorSt ') from dual;
Date Functions
last_day
Last day of the month
select last_day(SYSDATE) from dual;
add_months(d,n)
Adds or subtracts n months from date d
select add_months(SYSDATE, 2) from dual;
months_between(f,s)
Difference in months between date f and date s
select months_between(sysdate, '1-JAN-2006') from dual;
next_day(d,day)
Date that is the specified day of the week after d
select next_day(SYSDATE, 'Monday') from dual;
extract(c from d)
Extract date/time component c from expression d
select bdate,
extract (year from bdate) AS year_of_birth,
extract (month from bdate) AS month_of_birth,
extract (day from bdate) AS day_of_birth
from employee;
General Functions
greatest(a, b, ...)
greatest value of the function arguments
least(a, b, ...)
least value of the function arguments
select greatest(12*6, 148/2, 73), least(12*6, 148/2, 73) from dual;
nullif(a, b)
NULL if a = b; otherwise a
NVL(x, y)
y if x is NULL; otherwise x
Page 3 of 5
decode (x, a1, b1, a2, b2, ...., an, bn [, y])
b1 if x = a1, b2 if x = a2, .... bn if x = an, and otherwise y (or default:NULL)
Conversion Functions
to_char
converts any data type to character data using a format model (picture) eg. 'DD Mon YYYY' or '$9999.99'
select to_char(sysdate,'DD Mon YYYY') from dual;
to_number
converts a valid set of numeric character data to number data type
select 123, to_char(123, '$9999.99'), to_number('123') from dual;
to_date
converts character data of the proper format to date data type
uses format models - a character literal eg. dd-Mon-yyyy to control how Oracle interprets the string
Format Model elements for date/time conversion:
Y or YY or YYYY
Last one, two or four digits of year.
select to_char(sysdate, 'YYYY') from dual;
Q
MM, RM
Month(01-12), Roman numeral month (eg. IV for April)
select to_char(sysdate, 'MM) from dual;
select to_char(sysdate, 'RM') from dual;
Month
WW, W
Week of year, Week of month
select to_char(sysdate, 'WW') from dual;
select to_char(sysdate, 'W') from dual;
DDD, DD, D
Day of the year, month, week
select to_char(sysdate, 'DDD') from dual;
select to_char(sysdate, 'DD') from dual;
Quarter of year (Jan thru March = 1)
select to_char(sysdate, 'Q') from dual;
Name of month
select to_char(sysdate, 'Month') from dual;
Page 4 of 5
select to_char(sysdate, 'D') from dual;
DY, DAY
Abbreviated, full name of day
select to_char(sysdate, 'DY') from dual;
HH or HH12
Hour of day using 12 hour format
select to_char(sysdate, 'HH') from dual;
HH24
MI
SS
Hour of day using 24-hour clock
Minutes (0-59)
Seconds (0-59)
select to_char(sysdate, 'HH24:MI:SS') from dual;
Page 5 of 5