Functions in Oracle

Oracle provides some predefined functions that can be used in SQL:

Date function:

months_between(date1, date2), return months between date1 and date2. For example months_between('1/jan/09', '2/feb/09') will return 1.

sysdate will return today's date.

String function:

lpad(str, l, chr), left padding str with chr in length l. For example lpad('1', 3, '0') will return '001'.

rpad(str, l, chr), right padding str with chr in length l. For example rpad('1', 3, '0') will return '100'.

length(str), return the length of str. For example length('1234') will return 4.

substr(str, s, l), return portion of str, start from position s, follow by length l. For example substr('123456', 2, 2) will return 23.

Conversion function:

to_date(str, f), convert str to date in the format of f. For example to_date('1/7/2009', 'dd/mm/yyyy').

to_char(num), convert num to string.

to_number(str), convert str to number.

Group function:

count(c), return number of row where c is the column name.

sum(c), return the sum of value in column c where c should be a numeric column.

max(c), return the maximum value in column c.

min(c), return the minimum value in column c.

Others:

nvl(val, ret), return value of ret if val is null.

decode(val, cond1, ret1, cond2, ret2, ret3), same as:

if (val == cond1) return ret1

else if (val == cond2) return ret2

else return ret3