Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


Oracle SQL Functions


Oracle SQL provide buit-in SQL functions. SQL Functions take some values as a arguments, perform some function logic and returning some values. SQL inbuit function are so many that all are help us for no need to implementing your own logic.

Oracle SQL built-in functions are following,

SQL String 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.