SQL Functions
Built-in Oracle SQL functions are following,
Oracle SQL provide buit-in SQL functions. SQL Functions take some values as a arguments, perform some function logic and returning some values. SQL inbuilt function are so many that all are help us for no need to implementing your own logic.
SQL Numeric Functions
SQL numeric function take numeric value (maximum 38 digit) and return numeric value (maximum 38 digit). SQL numeric function return single row single column values. Following are some SQL numeric function.
| Function | Function Parameter | Description |
|---|---|---|
| ABS | ABS(n) | Function return absolute value of n values. |
| AVG | AVG(expression) | Function return average value of expression. |
| BITAND | BITAND | Function take bitwise vector value and return the bitwise AND vector value. |
| BIN_TO_NUM | BIN_TO_NUM(n, ...) | Function take any number of bit values. every value is must be either 0 or 1. This all bit values convert to a hexadecimal numeric value. |
| CEIL | CEIL(n) | Function return smallest integer round value that is greater then or equal to a parameter value (n). |
| COUNT | COUNT(*) COUNT(expression) |
Function return the number of rows in a SELECT statement. |
| FLOOR | FLOOR(n) | Function return largest integer round value that is equal to or less then to a parameter value (n). |
| LN | LN(n) | Function return exact log value (natural logarithm value). |
| LOG | LOG(n1,n2) | Function return log value (base on n1 value of n2 value). |
| MAX | MAX(expression) | Function return maximum value of expression. |
| MIN | MIN(expression) | Function return minimum value of expression. |
| MOD | MOD(n1,n2) | Function return the reminder value of n1 divide by n2. Where n1 and n2 is natural value. |
| NANVL | NANVL(value,alternative_value) | Function return the alternative value if the specified value is NaN (Not a number). If value not NaN then return original value. |
| POWER | POWER(n1,n2) | Function return n1 raised to a n2 power. n1 is base value and n2 is any numbers. |
| RAND | RAND() | Function generate random number between 0 to 1, but no longer support. Optionally use Rownum to fetch random record from database table. |
| REMAINDER | REMAINDER(n1,n2) | Function return the remainder value of n1 divide by n2. Where n1 and n2 is natural value. |
| ROUND | ROUND(n, decimal_number) | Function return the round number of specified nth number of decimal place. |
| SIGN | SIGN(n) | Function return the sign of the n number. |
| SQRT | SQRT(n) | Function return the square root of the n number. SQRT function return the real number. |
| SUM | SUM(expression) | Function return the sum of expression. |
| TRUNC | TRUNC(n, decimal_number) | Function return the truncated number of specified nth number of decimal place. |
SQL String Functions
SQL String function take some value and return string value. Function return data type VARCHAR2 if parameter argument CHAR or VARCHAR2 data type. Following are some SQL String function.
| Function | Function Parameter | Description |
|---|---|---|
| ASCII | ASCII( character ) | Function return the ASCII values of given argument. |
| CHR | CHR( number ) | Function return the ASCII value of the given argument value. |
| CONCAT | CONCAT( string1, string2 ) | Function concatenated string1 with string2 and return to a concatenated string. |
| INITCAP | INITCAP( char/string ) | Function return capitalize string/char (capitalize first letter of each word). |
| INSTR | INSTR( original_string, sub_string ) | Function return sub string position from the original string. |
| LENGTH | LENGTH( string ) | Function return the length of string. |
| LOWER | LOWER( char/string ) | Function return lowercase character in every word. |
| LTRIM | LTRIM( string, trim_char ) | Function remove all specified trim char from left side of the string. |
| NCHR | NCHR( ascii_number ) | Function return the ASCII value of the given argument value. |
| REPLACE | REPLACE( string, match_string, replace_string ) | Function return the string/char of every matched string replace with new string. |
| REGEXP_REPLACE | REGEXP_REPLACE( original_string, pattern ) | Function original string represent to a regular expression pattern. |
| REGEXP_SUBSTR | REGEXP_SUBSTR( original_string, pattern ) | Function return substring from the original string using regular expression pattern. |
| REGEXP_LIKE | REGEXP_LIKE( original_string, pattern ) | Function same as like condition but matching regular expression pattern to perform like condition. |
| RTRIM | RTRIM( string, trim_char ) | Function remove all specified trim char from right side of the string. |
| SUBSTR | SUBSTR( string, start_position, substr_length ) | Function return a selected string character from a full string. |
| TRANSLATE | TRANSLATE( string, match_char, replace_char ) | Function return the string/char of every matched character replace with new character. |
| TRIM | TRIM( string ) | Function remove all specified trim char from beginning and ending of the string. |
| UPPER | UPPER( char/string ) | Function return uppercase character in every word. |