Character/String Functions

Function Description Example
substr(src, p, l) Returns a portion of src starting at position p and l char. long substr('Hello',3,2) = 'll'
upper Converts to uppercase upper('Hello') = 'HELLO'
lower Converts to lowercase lower('HELLO') = 'hello'
initcap Capitalizes the first character or each word initcap('HELLO, world!') = 'Hello, World!'
ltrim Removes blanks at the left-end of a string ltrim(' Hello ') = 'Hello '
rtrim Removes blanks at the right-end of a string rtrim(' Hello' ) = ' Hello'
trim Removes blanks at either ends of a string trim(' Hello ') = 'Hello'
concat Concatenates two strings concat('Hel', 'lo') = 'Hello'
length Returns the length of a string length('Hello') = 5
replace(src, x, y) Replaces every occurrence of x in src by y (or by nothing if y is absent) replace('Hello', 'll', 'r') = 'Hero'
replace('Hello', 'o') = 'Hell'
lpad(src, l, p) Returns src, left-padded to length n with the string p lpad('Hello', 10, '>') = '>>>>>Hello'
rpad(src, l, p) Returns src, right-padded to length n with the string p rpad('Hello', 10, '!') = 'Hello!!!!!'
chr Returns a character's code chr('H') = 72
soundex Computes the soundex phonetic value of a string
translate Replaces each char. from x in src by the corresponding char. in y translate('hello', 'hl', 'HL') = 'HeLLo'
nls_initcap Same as initcap with National Language Support (NLS)
nls_upper Same as upper with National Language Support (NLS) nls_upper('Café) = CAFÉ
nls_lower Same as lower with National Language Support (NLS)
Date & Time Functions

Function Description Example
sysdate Returns the current date and time
trunc(d, t) Trancates a date d to the nearest time unit t (by default to the nearest day)
round(d, t) As before, but using rounding select round(sysdate) from dual
add_months(d, n) Returns d plus n months add_months(sysdate, 6)
months_between(d1, d2) Returns the number of months between dates d1 and d2
from_tz(d, tz) Adds a timezone component tz to date d
last_day(d) Returns the last day of the month containing d last_day(sysdate)