Oracle SQL Functions

SQL functions manipulate values and return a single value. If possible SQL functions will perform implicit datatype conversion. Most functions called with NULL will return NULL.

Function (argument1, argument2, ...)

Functions are scalar or aggregate in nature.

Scalar	Aggregate
Act on a single row	Act on a group of rows
Return a single value for each row queried	Return a single value for a group of rows
Can appear in a SELECT statement if the SELECT statement does not contain a GROUP BY clause	Can be used in select lists and HAVING clauses
Can appear in WHERE, START WITH and CONNECT BY clauses	If used in a SELECT with a GROUP BY clause then the GROUP BY must include all columns not affected by the function
Can be nested

Some functions do not return NULL values.

CONCAT	String concatenation
DECODE	Explicit conversions, translates values into specified codes.
DUMP	Dumps a value
NVL	NULL value substitution where if a NULL is found it is replaced with the given value
REPLACE	String replacement

DECODE

DECODE translates a column value pr expression into some other value. Certain values which the column value or expression may have can be specified. For each value list a code into which the value can be translated. If the column value or expression's value is not in the list of values then SQL uses the default code for translation.

DECODE (<column_name/expression>, <value_1>, <code_1>, <value_2>, <code_2>, ..., <value_n>, <code_n>, <default code>);

DECODE is neither a character nor a numeric function because it can return values of any type. DECODE could be used to determine a range in whicha value falls. The application of the DECODE function can present a code for that range. DECODE can effectively serve the same purpose as multiple IF-THEN_ELSE statements or a CASE or SWITCH statement if PL/SQL has a CASE or SWITCH statement.

SELECT	 NAME "Name"
	,SALARY "Salary"
	,DECODE
	(
		 TRUNC(SALARY/1000,0)
		,50,1
		,60,2
		,70,3
		,80,4
		,90,5
		,100,6
		,110,7
		,"Too Expensive"
	) "Shares"
	FROM EMPLOYEES;

DUMP

select owner, dump(table_name) from all_tables;

OWNER
------------------------------
DUMP(TABLE_NAME)
----------------------------------------------------------------------------------
SYS
Typ=1 Len=4: 68,85,65,76

SYS
Typ=1 Len=20: 83,89,83,84,69,77,95,80,82,73,86,73,76,69,71,69,95,77,65,80

SYS
Typ=1 Len=19: 84,65,66,76,69,95,80,82,73,86,73,76,69,71,69,95,77,65,80

SYS
Typ=1 Len=21: 83,84,77,84,95,65,85,68,73,84,95,79,80,84,73,79,78,95,77,65,80

Function Expression Precedence

Multiplication and division.
Addition and subtraction.
All remaining expressions are executed from left to right.

SQL Numeric Functions

ABS(n) - absolute value.
CEIL(n) - round up.
COS(n) - cosine.
COSH(n) - hyperbolic cosine.
EXP(n) - e raised to the n^th power.
FLOOR(n) - rounds down.
LN(n) - natural logarithm.
LOG(m, n) - log base m of n.
MOD(m, n) - remainder of m divided by n.
POWER(m, n) - m raised to the n^th power.
ROUND(n[, m]) - n rounded to m decimals.
SIGN(n) - n < 0 returns -1, n = 0 returns 0 and n > 0 return 1.
SIN(n) - sine.
SINH(n) - hyperbolic sine.
SQRT(n) - square root.
TAN(n) - tangent.
TANH(n) - hyperbolic tangent.
TRUNC(n[, m]) - n truncated to m decimal places.

Character Value Returning Character Functions

CHR(n) - returns the character of the ASCII value.
CONCAT(char1, char2) - char1 concatenated to char2.
INITCAP(char) - uppercase on first letter of each word.
LOWER(char) - lowercase.
LPAD(char1, n[, char2]) - char1 left-padded either with blanks or the value of char2.
LTRIM(char1[, set]) - char1 left-trimmed of blanks or set characters.
NLS_INITCAP(char[, 'nlsparams']) - char with all initial letters capitalised. nlsparams is NLS_SORT or BINARY.
NLS_LOWER(char[, 'nlsparams']) - lowercase. nlsparams is NLS_SORT or BINARY.
NLS_UPPER(char[, 'nlsparams']) - uppercase. nlsparams is NLS_SORT or BINARY.
REPLACE(char, search_string[, replacement_string]) - replace all instances of search_string in char with replacement_string.
RPAD(char1, n[, char2]) - char1 right-padded either with blanks or the value of char2.
RTRIM(char1[, set]) - char1 right-trimmed with blanks or set characters.
SOUNDEX(char) - SOUNDEX equivalent of char, ie. sounds-like. SOUNDEX converts a character string to a phonetic value. SOUNDEX can retrieve values which start with the same letter and sound the same as (are phonetically similar to) the search string. Thus Si would produce answers such as See or Sea.
SUBSTR(char m[, n]) - substring located in char starting at m and ending at n.
SUBSTRB(char m[, n]) - byte substring located in char starting at m and ending at n.
TRANSLATE(char, from, to) - char with values in from translated to the values in to.
UPPER(char) - uppercase.

Numeric Value Returning Character Functions

ASCII(char) - ASCII number of first character.
INSTR(char1, char2[, n[, m]]) - numeric position of m^th occurence of char2 in char1 starting at position n. If n is negative search backwards.
INSTRB(char1, char2[, n[, m]]) - numeric byte position of n^th occurence of char2 in char1 starting at position n. If n is negative search backwards.
LENGTH(char) - number of characters in char.
LENGTHB(char) - length in bytes of char.
NLSSORT(char[, 'nlsparams') - string of bytes used to sort char.

Date and Time Functions

Subtraction of one date value from another when both dates are passed through the TO_DATE function gives the number of days between the two dates. Use of dates in queries requires use of the TO_DATE() function in order to convert between external strings and internal Oracle date representation.

Oracle stores dates internally in Oracle7 as a seven-byte field containing two decimal digits per byte.

year	month	day	hour	minute	second
YYYY (2 bytes)	MM	DD	hh	mi	ss

New Year's eve 1999 would be stored in Oracle internally as shown below.

Bytes	Value
1	19
2	99
3	12
4	31
5	23
6	45
7	00

ADD_MONTHS(d, n) - add n months to date d.
LAST_DAY(d) - last day of the month of date d.
MONTHS_BETWEEN(d1, d2) - either positive or negative difference between date d1 and d2.
NEW_TIME(d, z1, z2) - date and time in time zone z2 for date d in time zone z1.
NEXT_DAY(d, char) - first weekday in char later than d.

ROUND(d[, fmt]) - date d rounded to the unit in fmt string. For example,

ROUND(TO_DATE('28-FEB-99 11:15 PM', 'DD-MON-YY HH:MI PM')) gives 01-MAR-99 12:00 AM.

SYSDATE - system date.
TRUNCDATE(d[, fmt]) - date d with date and time truncated to format in fmt string. For Example,
```
TRUNCDATE(TO_DATE('29-FEB-00 11:15 PM', 'DD-MON-YY HH:MI PM')) gives 29-FEB-00.
```

Conversion Functions

In general implicit datatype conversions are performed between compatible datatypes. Explicit datatype conversions can help performance.

CHARTOROWID(char) - char to internal ROWID.
CONVERT(char, dest_char_set[, source_char_set]) - char to character set specified in dest_char_set. source_char_set must be specified if char is a different character set to that of the database default.
HEXTORAW(char) - hexadecimal to raw.
RAWTOHEX(raw) - raw to hexadecimal.
ROWIDTOCHAR(rowid) - ROWID to character equivalent.
TO_CHAR(d, [,fmt [, 'nlsparams' ]]) or TO_CHAR(label[, fmt]) or TO_CHAR(number) - can convert date or label values specified by format in fmt or a number to characters. For example,
```
TO_CHAR(TO_DATE('12-DEC-99'), 'DDth "of" MONTH, YYYY') gives 12th of DECEMBER, 1999.
```

TO_DATE(char[, fmt[, 'nlsparams']]) - converts char to a date datatype according to format in fmt.

Format	Description
YYYY	Four digit year
YY	Two digit year
MM	Two digit month
MON	Upper case three character month
Month	Mixed case month
DD	Two digit day
DAY	Upper case day
DDth	Two digit day with st, nd or rd.

TO_LABEL(char[, fmt]) - converts char to a LABEL datatype using fmt as a format.
TO_MULTI_BYTE(char) - converts character value in char to its equivalent multibyte value.
TO_NUMBER(char[, fmt[, 'nlsparams']]) - converts char to a number according to the format in fmt.
TO_SINGLE_BYTE(char) - converts multibyte value in char to its single-byte equivalent.

Grouping Functions

Group functions act on groups of values and not individual items. Use group functions in conjunction with SELECT, INSERT, UPDATE and DELETE statements.

DISTINCT - returns unique values or rows from a set. A SELECT DISTINCT statement will return the first row in each duplicated group.
ALL - returns all values or rows including all duplicates.
AVG([DISTINCT|ALL]n) - average value for a given set of numbers.
COUNT({^* | [DISTINCT|ALL] expr}) - counts rows in set of records.
MAX([DISTINCT|ALL] expr) - maximum of specification.
MIN([DISTINCT|ALL] expr) - minimum of specification.
STDDEV([DISTINCT|ALL] expr) - standard deviation. The standard deviation is the average difference or variance from a mean or average value.
SUM([DISTINCT|ALL] expr) - an accumulation or summation of values.
VARIANCE([DISTINCT|ALL] expr) - a variance is an average deviation from a mean or average.

Various Other Functions

DUMP(expr[, return_format[, start_position[, length]]]) - returns a string containing datatype code, byte length and internal expr representation. return_format specifies output as octal, decimal, hexadecimal or single characters.
GREATEST(expr[, expr] ... ) - the greatest value between two expressions.
LEAST(expr[, expr] ... ) - the least value between two expressions.
NVL(expr1, expr2) - returns the value in expr2 when the value of expr1 is NULL.
UID - current user ID.
USER - current user name.
USERENV(option) - current environment setting based on option.
- OSDBA - TRUE if user has OSDBA role enabled.
- LABEL - current session label.
- LANGUAGE - session language and territory.
- TERMINAL - terminal session operating system ID.
- SESSIONID - auditing session ID.
- ENTRYID - available auditing entry identifier.
- CLIENT_INFO - V$SESSION setting of CLIENT_INFO for this session.
- LANG - session ISO language.

VSIZE(expr) - number of bytes in the internal representation of expr.

select owner, vsize(table_name) from all_tables;

OWNER                          VSIZE(TABLE_NAME)
------------------------------ -----------------
SYS                                            4
SYS                                           20
SYS                                           19
SYS                                           21
SYS                                           13
SYS                                            8
SYSTEM                                        13
SCOTT                                          4
SCOTT                                          3
SCOTT                                          5
SCOTT                                          8
DEMO                                           8
DEMO                                          10
DEMO                                           3
DEMO                                           8
DEMO                                          12
DEMO                                           7
DEMO                                           5
DEMO                                           8
DEMO                                          11
DEMO                                           4

Expression (expr) Clauses

Expressions can exist in many forms and can be used in the following situations.

SELECT statement select list.
WHERE and HAVING clause conditions.
CONNECT BY, START WITH and ORDER BY clauses.
INSERT command VALUES clause.
UPDATE command SET clause.