Oracle Functions

Oracle Functions : Oracle functions has many built-in functions. Oracle String Functions,

Oracle String Functions

Function Description
ASCII Returns the number code that represents the specified character
ASCIISTR Converts a string in any character set to an ASCII string using the database character set
CHR Returns the character based on the number code
COMPOSE Returns a Unicode string
CONCAT Allows you to concatenate two strings together
Concat with || Allows you to concatenate two or more strings together
CONVERT Converts a string from one character set to another
DECOMPOSE Accepts a string and returns a Unicode string
DUMP Returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression
INITCAP Sets the first character in each word to uppercase and the rest to lowercase
INSTR Returns the location of a substring in a string
INSTR2 Returns the location of a substring in a string, using UCS2 code points
INSTR4 Returns the location of a substring in a string, using UCS4 code points
INSTRB Returns the location of a substring in a string, using bytes instead of characters
INSTRC Returns the location of a substring in a string, using Unicode complete characters
LENGTH Returns the length of the specified string
LENGTH2 Returns the length of the specified string, using UCS2 code points
LENGTH4 Returns the length of the specified string, using UCS4 code points
LENGTHB Returns the length of the specified string, using bytes instead of characters
LENGTHC Returns the length of the specified string, using Unicode complete of characters
LOWER Converts all letters in the specified string to lowercase
LPAD Pads the left-side of a string with a specific set of characters
LTRIM Removes all specified characters from the left-hand side of a string
NCHR Returns the character based on the number code in the national character set
REGEXP_INSTR Returns the location of a regular expression pattern in a string
REGEXP_REPLACE Allows you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching
REGEXP_SUBSTR Allows you to extract a substring from a string using regular expression pattern matching
REPLACE Replaces a sequence of characters in a string with another set of characters
RPAD Pads the right-side of a string with a specific set of characters
RTRIM Removes all specified characters from the right-hand side of a string
SOUNDEX Returns a phonetic representation (the way it sounds) of a string
SUBSTR Allows you to extract a substring from a string
TRANSLATE Replaces a sequence of characters in a string with another set of characters
TRIM Removes all specified characters either from the beginning or the end of a string
UPPER Converts all letters in the specified string to uppercase
VSIZE Returns the number of bytes in the internal representation of an expression

 

Oracle Numeric Functions

Function Description
ABS Returns the absolute value of a number
ACOS Returns the arc cosine of a number
ASIN Returns the arc sine of a number
ATAN Returns the arc tangent of a number
ATAN2 Returns the arc tangent of n and m
AVG Returns the average value of an expression
BITAND Returns an integer representing an AND operation on the bits of expr1 and expr2
CEIL Returns the smallest integer value that is greater than or equal to a number
COS Returns the cosine of a number
COSH Returns the hyperbolic cosine of a number
COUNT Returns the count of an expression
EXP Returns e raised to the power of number
FLOOR Returns the largest integer value that is equal to or less than a number
GREATEST Returns the greatest value in a list of expressions
LEAST Returns the smallest value in a list of expressions
LN Returns the natural logarithm of a number
LOG Returns the natural logarithm of a number to a specified base
MAX Returns the maximum value of an expression
MEDIAN Returns the median of an expression
MIN Returns the minimum value of an expression
MOD Returns the remainder of n divided by m
POWER Returns m raised to the nth power
REGEXP_COUNT Counts the number of times that a pattern occurs in a string
REMAINDER Returns the remainder of m divided by n
ROUND Returns a number rounded to a certain number of decimal places
ROWNUM Returns a number that represents the order that a row is
SIGN Returns a value indicating the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SUM Returns the summed value of an expression
TAN Returns the tangent of a number
TANH Returns the hyperbolic tangent of n
TRUNC Returns a number truncated to a certain number of decimal places

Oracle Date Functions

Function Description
ADD_MONTHS Returns a date with a specified number of months added
CURRENT_DATE Returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command
CURRENT_TIMESTAMP Returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command
DBTIMEZONE returns the database time zone as a time zone offset or a time zone region name
EXTRACT Extracts a value from a date or interval value
LAST_DAY Returns the last day of the month based on a date value
LOCALTIMESTAMP Returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command
MONTHS_BETWEEN Returns the number of months between date1 and date2
NEW_TIME Converts a date from time zone1 to a date in time zone2
NEXT_DAY Returns the first weekday that is greater than a date
ROUND Returns a date rounded to a specific unit of measure
SESSIONTIMEZONE Returns the current session’s time zone as a time zone offset or a time zone region name
SYSDATE Returns the current system date and time on your local database
SYSTIMESTAMP Returns the current system date and time (including fractional seconds and time zone) on your local database
TRUNC Returns a date truncated to a specific unit of measure
TZ_OFFSET Returns the time zone offset of a value