FUNCTIONS

Here one can find a list of all the available Oracle 11g release 2 SQL functions http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions.htm#i1482196
In SQL Developer one can go in the Menu to View -> Snippets and a list of available functions will be shown in the window.

MOSTLY USED FUNCTIONS

MIN() – returns the minimum value
MAX() – returns maximum value
AVG() – returns average value
SUM() – returns the sum of all values
COUNT() – returns the number of elements

TODO: learn default values of function parameters

Operating on character data
LOWER – lower(‘SQL’) = sql
UPPER – upper(‘sql’) = SQL
INITCAP – initcap(‘sql’) = Sql
LENGTH – length(‘A short string’) = 14
CONCAT – concat(‘SQL is’,’ easy to learn.’) = SQL is easy to learn.
SUBSTR(string, start position, number of characters) – substr(‘http://www.domain.com’,12,6) = domain
substr(‘http://www.domain.com’,12) = domain.com DEFAULT for number of characters (returns until the end of the string)

INSTR(source string, search item, [start position],[nth occurrence of search item]) – instr(‘http://www.domain.com’,’.’,1,2) = 18
instr(‘http://www.domain.com’,’.’) = 11 DEFAULT for start position 1, for nth occurrence 1

LPAD(string, length after padding, padding string) – lpad(‘#PASSWORD#’,11,’#’) = ##PASSWORD#
lpad(‘#PASSWORD#’,11) = ‘ #PASSWORD#’ DEFAULT for padding string is SPACE

RPAD(string, length after padding, padding string) – rpad(‘#PASSWORD#’,11,’#’) = #PASSWORD##
rpad(‘#PASSWORD#’,11) = ‘#PASSWORD# ‘ DEFAULT for padding string is SPACE

TRIM – trim( [LEADING | TRAILING | BOTH] ‘#’ from ‘#PASSWORD#’) = PASSWORD
trim(‘#PASSWORD#’) = ‘#PASSWORD#’ trim(‘ PASSWORD ‘) = ‘PASSWORD’ DEFAULT it would only trim SPACE signs

REPLACE(string, search item, replacement item) – replace(‘#PASSWORD#’,’WORD’,’PORT’) = #PASSPORT#
replace(‘#PASSWORD#’,’WORD’) = #PASS# DEFAULT for replacement item is empty string, or deleting of the search item

Operating on numeric data
ROUND(number, decimal precision) – round(42.39,1) = 42.4
round(42.39) = 42 DEFAULT for decimal precision is 0

TRUNC(number, decimal precision) – trunc(42.39,1) = 42.3
trunc(42.39) = 42 DEFAULT for decimal precision is 0

MOD(dividend, divisor) – mod(42,10) = 2

Operating on date information
MONTHS_BETWEEN – months_between(’01-FEB-2008′,’01-JAN-2008′) = 1
ADD_MONTHS – add_months(’01-JAN-2008′,1) = 01-FEB-2008
LAST_DAY – last_day(’01-FEB-2008′) = 29-FEB-2008
NEXT_DAY – next_day(’01-FEB-2008′,’Friday’) = 08-FEB-2008
SYSDATE (returns the current server date and time) – sysdate = 17-DEC-2012
ROUND(date, date precision format) – round(sysdate,’month’) = 01-JAN-2008
TRUNC(date, date precision format) – trunc(sysdate,’month’) = 01-DEC-2007 DEFAULT precision is day

Conversion functions
TO_CHAR(number1, [format], [nls_parameter])
select to_char(00001,’0999999′)||’ is a special number’
from dual; RETURNS: ‘0000001 is a special number’

TO_DATE(string1, [format], [nls_parameter])
select to_date(’25-DEC-2010 18:03:45′, ‘DD-MON-YYYY
HH24:MI:SS’) from dual;

TO_NUMBER(string1, [format], [nls_parameter])
select to_number(‘$1,000.55′,’$999,999.99′) from dual; RETURNS: 1000.55
If you convert a number using a shorter
format mask, an error is returned. If you
convert a number based on a longer format
mask, the original number is returned.

General functions
NVL(original,ifnull) – where original represents the term being tested and ifnull is the result
returned if the original term evaluates to null
select nvl(null,1234) from dual; RETURNS: 1234

NVL2(original, ifnotnull, ifnull)
select nvl2(1234,1,’a string’) from dual; RETURNS: ORA-01722: invalid number
select nvl2(null,1234,5678) from dual; RETURNS: 5678

NULLIF(ifunequal, comparison_term)
select nullif(1234,1234) from dual; RETURNS: null
select nullif(1234,123+1) from dual; RETURNS: 1234
select nullif(’24-JUL-2009′,’24-JUL-09′) from dual; RETURNS: 24-JUL-2009 (they are seen as 2 strings, not dates)

COALESCE(expr1, expr2,…,exprn) – expr1 is returned if it is not null, else expr2 if it is not null, and so on.
COALESCE(expr1,expr2) = NVL(expr1,expr2)
COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))
select coalesce(null, null, null, ‘a string’) from dual; RETURNS: a string
select coalesce(null, null, null) from dual; RETURNS: null

Conditional functions
DECODE(expr1,comp1, iftrue1, [comp2,iftrue2…[ compN,iftrueN]], [iffalse])
select decode(1234,123,’123 is a match’) from dual; RETURNS: null
select decode(1234,123,’123 is a match’,’No match’) from dual; RETURNS: No match
select decode(‘search’,’comp1′,’true1′, ‘comp2′,’true2’, ‘search’,’true3′, substr(‘2search’,2,6)),’true4′, ‘false’) from dual; RETURNS: true3 (when a match is found, no further search is done)

CASE search_expr
WHEN comparison_expr1 THEN iftrue1
[WHEN comparison_expr2 THEN iftrue2
…
WHEN comparison_exprN THEN iftrueN
ELSE iffalse]
END

select
case substr(1234,1,3)
when ‘134’ then ‘1234 is a match’
when ‘1235’ then ‘1235 is a match’
when concat(‘1′,’23’) then concat(‘1′,’23’)||’ is a match’
else ‘no match’
end
from dual;
RETURNS: 123 is a match

CASE
WHEN condition1 THEN iftrue1
[WHEN condition2 THEN iftrue2
…
WHEN conditionN THEN iftrueN
ELSE iffalse]
END

select last_name, hire_date,
trunc(months_between(sysdate,hire_date)/12) years,
trunc(months_between(sysdate,hire_date)/60) “Years divided by 5”,
case
when trunc(months_between(sysdate,hire_date)/60) < 1 then ‘Intern’
when trunc(months_between(sysdate,hire_date)/60) < 2 then ‘Junior’
when trunc(months_between(sysdate,hire_date)/60) < 3 then ‘Intermediate’
when trunc(months_between(sysdate,hire_date)/60) < 4 then ‘Senior’
else ‘Furniture’
end Loyalty
from employees
where department_id in (60,10);

Group functions
COUNT({*|[DISTINCT|ALL] expr}) – counts the number of rows in a group
When COUNT(*) is invoked, all rows in the group, including those with nulls
or duplicate values are counted. When COUNT(DISTINCT expr) is executed, only
unique occurrences of expr are counted for each group. The ALL keyword is part of the
default syntax, so COUNT(ALL expr) and COUNT(expr) are equivalent. These count
the number of nonnull occurrences of expr in each group.
select count(*) from employees – RETURNS: 107
select count(commission_pct) from employees – RETURNS: 45
select count(distinct commission_pct) from employees – RETURNS: 7
select count(hire_date), count(manager_id) from employees RETURNS: 107, 106 – number of nonnull values

AVG([DISTINCT|ALL] expr) – caunts the average value of numeric column or expression in a group. Data type for expr is NUMBER
If the average value for COMMISSION_PCT is retrieved from the EMPLOYEES table, only the nonnull values are considered. The expression AVG(COMMISSION_PCT) adds the 35 nonnull COMMISSION_PCT values and divides the total by 35. The average based on all 107 rows may be computed using the expression AVG(NVL(COMMISSION_PCT,0)).

SUM([DISTINCT|ALL] expr) – returns the aggregated total of the nonnull numeric
expression values in a group. Data type for expr is NUMBER
select sum(2) from employees – there is 107 rows in table, adds 2 for every row RETURNS: 214

MAX([DISTINCT|ALL] expr) – returns the maximum value from a row
MIN([DISTINCT|ALL] expr) – returns the minimum value from a row
The data type of the expr parameter may be NUMBER, DATE, CHAR or VARCHAR2.

Nested group functions
G1(G2(group_item ) = result
G1(G2(G3(group_item))) is NOT allowed