Show Menu

SQL retrieval functions 1 Cheat Sheet by

from the book: Beginning Oracle SQL for Oracle Database 12c

Arithmetic functions

round (n[,m])
Round n on m decimal positions, default m=0
trunc (n[,m])
Truncate n on m decimal positions, default m=0
ceil (n)
Round n upward to an integer
floor (n)
Round n downward to an integer
abs (n)
Absolute value of n
sign (n)
-1, 0, 1 if n is negative, zero, or positive
sqrt (n)
Square root of n
exp (n)
e raised to the nth power
ln(n), log(m, n)
Natural logarithm, and logarithm base m
power (n,m)
n raised to the mth power
mod (n,m)
Remainder of n divided by m (m can be 0)
sin(n), cos(n), tan(n)
Sine, cosine and tangent of n (radians)
asin(n), acos(n), atan(n)
Arcsine, arccosine, and arctangent of n
sinh(n), cosh(n), tanh(n)
Hyperbolic sine, hyperbolic cosine and hyperbolic tangent of n

Text functions 1

lenth (t)
Length (in charac­ters) of t
ascii (t)
ASCII value of first character of t
chr (n)
Character with ASCII value of n
upper(t), lower(t)
t in upperc­ase­/lo­wercase
Each word in t with initial uppercase, remainder in lowercase
ltrim (t[,k])
Remove characters from the left of t, until the first character not in k
rtrim (t[,k])
Remove characters from the right of t, after the last character not in k
trim ([[option] [c from]]t)
Trim character c from t; option = leading, trailing, or both
lpad (t,n[,k])
Left-pad t with sequence of characters in k to length n
rpad (t,n[,k])
Right-pad t with k to length n; default k=space
concat (t1,t2)
Concat­enate t1 and t2 (equiv­alent to ||)

Regexp options

Case-i­nse­nsitive search
Case-s­ens­itive search
Allow . to match the newline character
Treat text as multiple lines; ^ and $ refer to the beginning and the end of those lines
If you specify confli­cting combin­ations, such as ic, the Oracle DBMS uses the last value (c) and ignores the first one.

Text functions 2

substr (t,n[,m])
Substring of t from position n, m character long; default m=end
instr (t,k)
Position of the first occurrence of k in t
instr (t,k,n)
n=starting position
instr (t,k,n,m)
m=mth occurrence of k
translate (t,v,w)
Replace characters from v (occurring in t) by corres­ponding character in w
replace (t,v)
Remove each occurrence of v from t
replace (t,v,w)
Replace each occurrence of v in t by w

Regular expres­sions

regexp­_like (text, pattern[, options])
regexp­_instr (text, pattern[, pos[,
occurrence[, return[, option­s]]]])
regexp­_substr (text, pattern[, pos[,
occurrence[, option­s]]])
regexp­_re­place (text, pattern[, replace[, pos[, occurr­ence[, option­s]]]])


Position of the first character of the pattern found (default)
Position of the first character after the pattern found


No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          Oracle SQL Developer Keyboard Shortcuts
          SQLDev KB Shortcuts OS X Cheat Sheet
          DBeaver SQL Editor Keyboard Shortcuts

          More Cheat Sheets by chunyan