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