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 the range of common Oracle SQL function that may
be used for this unit:
Arithmetic Functions
abs(n)
The column's absolute value
select abs(sallower - salupper) from salgrade;
ceil(n)
Nearest whole integer greater than or equal to number
select ceil(10.6) from dual;
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;
Page 1 of 6
sign(n)
If n=0, returns 0; if n>0, returns 1; if n<0, returns -1
select sign(12 - 45) from dual;
sqrt(n)
Square root of n
select sqrt(120) from dual;
select round(sqrt(120),2) from dual;
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;
Page 2 of 6
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;
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;
trim(char)
remove leading and trailing blanks (spaces) from char
select trim(' Intro to SQL ') 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;
Page 3 of 6
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
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
Quarter of year (Jan thru March = 1)
select to_char(sysdate, 'Q') from dual;
Page 4 of 6
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
Name of month
select to_char(sysdate, 'Month') from dual;
Page 5 of 6
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;
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
Hour of day using 24-hour clock
MI
Minutes (0-59)
SS
Seconds (0-59)
select to_char(sysdate, 'HH24:MI:SS') from dual;
Page 6 of 6