These functions return a value for each record for a queried table or a view. There are 7 main categories listed below.
- Numeric
- Character
- returning number
- returning character
- Date / Date time
- NULL related
- Comparison
- Conversion
- Hierarchical
In tables below, you can find the most often used functions. For the complete list please refer to Oracle documentation pages.
Numeric functions
Numeric functions work with numeric data only – that means, they require a numerical value and return number.
| Function | Description | Usage |
|---|---|---|
| ABS | returns an absolute value of a number | SELECT abs(-1) as abs_value FROM dual; |
| CEIL | always rounds up a given integer if it is not a whole number already. | SELECT ceil(10.1) as ceil FROM dual; |
| FLOOR | always rounds down a given interger if it is not a whole number already | SELECT floor(10.9) as floor FROM dual; |
| ROUND | accepts two parameter. First parameter is a given integer and the second one defines the precision for rounding (if omitted, it is rounded to zero decimal places) or number of decimal numbers after the decimal points if you will. If the second parameter is negative the round mechanism is applied to the left side of the decimal point. | SELECT round(10.92,1) as round FROM dual; |
| TRUNC (number) | returns a truncated integer to a specific set of decimal points if a given parameter is numeric (there is an equivalent of this function accepting date parameter .. see below for more details). If the second parameter is negative then the truncate mechanism is applied to the left side of the decimal point. | SELECT trunc(10.92,1) as trunc FROM dual; |
Character
Character functions divide into two categories based on whether they return a character or numeric value.
Returning character values
Character functions returning character values return either VARCHAR2 (for input parameters of CHAR, VARCHAR2) or NVARCHAR2( for input parameters of NCHAR, NVARCHAR2).
| Function | Description | Usage |
|---|---|---|
| CHR | return a character based on its binary representative (as a parameter) | SELECT chr(65) as chr FROM dual |
| CONCAT | gets two parameters and returns their concatenation (join them together) | SELECT concat('Oracle ','World') as concatFROM dual |
| INITCAP | returns a given string with the first letter capitalized for each word in the string. Words are determined by a space or non-alphanumeric character. | SELECT initcap('oracle_WORLD example') as initcap FROM dual |
| LOWER | returns a given string with all letters lowercase. | |
| LPAD | returns a given string (parameter1) left-padded by number of characters (parameter2) given as a last (parameter3) parameter. | SELECT lpad('Oracle World', 20, 'x') as lpad FROM dual |
| LTRIM | removes from the left side all defined characters (if not defined, single blank is used) from a given string. | SELECT ltrim('---Oracle World---','-') as ltrim FROM dual |
| REGEXP_REPLACE | extends REPLACE function by using regular expressions. VERY HEAVY ON CPU!! Do not use in complex queries! | SELECT regexp_replace('Ora1 _orld', '[[:digit:]]','cle') as regexp_replace FROM dual |
| REGEXP_SUBSTRING | extends SUBSTR function by using regular expressions. VERY HEAVY ON CPU!! Do not use in complex queries! | SELECT regexp_substr('http://www.oracle-world.com', '[a-z.-]+',12) as regexp_substr FROM dual |
| REPLACE | returns a given string (parameter1) with a replaced subset of characters (parameter2) with a given replacement (parameter3). Unlike TRANSLATE, it can replace one character with more characters | SELECT replace('Oz World', 'z','racle') as replace FROM dual |
| RPAD | returns a given string (parameter1) right-padded by number of characters (parameter2) given as a last (parameter3) parameter. | SELECT rpad('Oracle World', 20, 'x') as rpad FROM dual |
| RTRIM | removes from the right side all defined characters (if not defined, single blank is used) from a given string. | SELECT rtrim('---Oracle World---','-') as rtrim FROM dual |
| SUBSTR | returns a subset of characters from a given string (parameter1) where the second parameter defines whether Oracle will start from the beginning (positive number; 0 is considered as 1) or backwards from the end (negative number) and from which position. The last (parameter3) defines how many characters should be extracted. | SELECT substr('Oracle World',1,6) as substr FROM dual |
| TRANSLATE | returns a given string (parameter1) with a replaced subset of characters (parameter2) with a given replacement (parameter3). It can only replace one character by another. | SELECT translate('Oxacle Woxld', 'x','r') as translate FROM dual |
| TRIM | returns a trimmed string given as a first parameter. You can define whether you want to trim leading or trailing characters or both. If no specification is given, it trims leading and trailing spaces. | SELECT trim(LEADING '-' FROM '---Oracle World---') as trim FROM dual |
| UPPER | returns a given string with all letters uppercase. | SELECT upper('ORACLE World') as upper FROM dual |
Returning numeric values
Character functions returning numeric values. They accept any character type as an input parameter.
| Function | Description | Example |
|---|---|---|
| ASCII | Returns a decimal representation of a given (single) character. Does not accept CLOBs. | SELECT ascii('a') as ascii FROM dual |
| INSTR | Returns a position in a given string based on given criteria. First parameter is a given string, second parameter is a string / character we are looking for in a string, third parameter is a starting position (if negative it is processed backwards) and the last parameter is n-th occurence of in the string of a lookup value. | SELECT instr('oracle world', 'o', 1, 2) as instr FROM dual |
| LENGTH | returns the length of a given string. Considers even leading, trailing spaces and other non-printable characters (carriage return, ...) | SELECT length('Oracle World') as length FROM dual |
| REGEXP_COUNT | returns the number of occurrences based on a regular expression. | SELECT regexp_count('Oracle World :: Developer''s Cheat Sheet', '[ ][A-P]', 1, 'c') as regexp_count FROM dual |
| REGEXP_INSTR | extends INSTR function by using regular expressions. VERY HEAVY ON CPU!! Do not use in complex queries! | SELECT regexp_instr('Oracle World :: Developer''s Cheat Sheet', '[ ]', 1, 3) as regexp_instr FROM dual |
Date / Date time functions
Returns a date, datetime or timestamp format.
| Function | Description | Example |
|---|---|---|
| ADD_MONTHS | add a specific number of months (negative number deducts) to a given date | SELECT add_months(DATE '2010-01-01', 3) as add_months FROM dual |
| LAST_DAY | returns last day in a month for a given date | SELECT last_day(DATE '2010-01-03') as last_day FROM dual |
| MONTHS_BETWEEN | returns number of months between two given dates | SELECT months_between(DATE '2015-07-14', DATE '2010-01-03') as months_between FROM dual |
| NEXT_DAY | returns the next date for a specified week day starting from a given date as a first parameter. | SELECT next_day(DATE '2015-07-14','MONDAY') as next_day FROM dual |
| ROUND | returns a date rounded to the specified date-unit. | SELECT round(DATE '2015-07-14','YEAR') as round FROM dual |
| SYSDATE | returns the current datetime from the database server. | SELECT sysdate as act_date FROM dual |
| TRUNC | returns a truncated date to the specified unit. (always down) | SELECT trunc(DATE '2015-03-10','MM') as trunc FROM dual |
NULL-related functions
These functions handle NULL values.
| Function | Description | Example |
|---|---|---|
| COALESCE | returns the first non-null value from a given list of values (min. 2 values required) | SELECT coalesce(NULL, NULL, 'Oracle', NULL, 'World') as coalesce FROM dual |
| NVL | returns the second given value if the first is NULL. If the first is a non-null value, it returns the first one. | SELECT nvl(NULL, 'Oracle World') as nvl FROM dual |
| NVL2 | returns the second parameter if the given value is not null. If the given value is null, it returns the third parameter. | SELECT nvl2(NULL, 'Oracle World', 'Developer''s Cheat Sheet') as nvl2 FROM dual |
Comparison functions
They determine the greatest or least value from a given set of values.
| Function | Description | Example |
|---|---|---|
| GREATEST | returns the greatest value of a given set of values. | SELECT greatest(1,5,31,2,20,3,39,3) as greatest FROM dual |
| LEAST | returns the least value of a given set of values. | SELECT least(1,5,31,2,20,3,39,3) as least FROM dual |
Conversion functions
Conversion functions convert values from one data type to another.
| Function | Description | Example |
|---|---|---|
| HEXTORAW | converts hexadecimal values to a character values. | SELECT UTL_RAW.CAST_TO_VARCHAR2(hextoraw('4F7261636520576F726C64')) as hextoraw FROM dual |
| NUMTODSINTERVAL | converts a given parameter to a "DAY TO SECOND" interval. | SELECT numtodsinterval(10, 'SECOND') as numtodsinterval FROM dual |
| NUMTOYMINTERVAL | converts a given parameter to a "YEAR TO MONTH" interval. | SELECT numtoyminterval(31, 'MONTH') as numtoyminterval FROM dual |
| RAWTOHEX | converts character values to a hexadecimal value | SELECT rawtohex('OracLe World') as rawtohex FROM dual |
| TO_CHAR | accepts three data types: Character, Number, Date. Character: Converts a given set of character to a STRING Number: Converts a given number to a STRING (trims leading zeros). You can set a format for an output value Date: Converts a given date to a STRING specified by a given format. | SELECT to_char('L1001') as to_char FROM dual |
| TO_DATE | returns a data type DATE / DATETIME from a given string and format. | SELECT to_date('11/20/2010 18:34:38 PM', 'MM/DD/YYYY HH24:MI:SS') as to_date FROM dual |
| TO_NUMBER | return a NUMBER data type from a given string. You can specify the format of a number by providing a second parameter. | SELECT to_number('123456.789', '999999.999') as to_number FROM dual |
Hierarchical functions
Hierarchical functions provide a hierarchical path for a result data set.
| Function | Description | Example |
|---|---|---|
| SYS_CONNECT_BY_PATH | returns path from a root node to a leaf node. It can be applied in hierarchical queries only. | SELECT level as id_row |