SQL Functions

Aggregate Functions
  • AVG() – Returns the average value
  • COUNT() – Returns the number of rows
  • FIRST() – Returns the first value
  • LAST() – Returns the last value
  • MAX() – Returns the largest value
  • MIN() – Returns the smallest value
  • SUM() – Returns the sum
  • STDDEV – Returns standard deviation of column
  • VARIANCE – Returns the variance of N.
Character Functions 
  • LOWER – Converts alpha characters values to lowercase.
  • UPPER  – Converts alpha characters values to uppercase.
  • CONCAT – Concatenates the first character value to the second character value.
  • SUBSTR – Returns specified characters from character value, string from a specified position m to n charaters long.
  • LENGTH – Returns the number of characters in a value.
  • INSTR – Returs the numaric position of a named character.
  • INITCAP – Returns a string with the first letter of each word in upper case, keeping all othered.
  • LPAD – Pads the character value right- specified to a total width of n character positions.
  • RPAD – Pads the character value left – specified to total width of n character positions.
  • TRIM – Trims heading or tailing charecters ( or both) from a specified string.
  • LTRIM – It enables to trim heading characters from a character string.
  • RTRIM – It enables to trim tailing characters from a character string.
  • REPLACE – Returns the every occurences of search string replace by the the replacement string.
  • TRANSLATE – Used to translate character by character in a string.
  • CHAR – Returns a character having the binary equivalent to n.
  • ASCII – Returns the decimalrepresentation in the character database set of the first character of the char.
Number Functions :
  • ROUND – Returns the m round to n places right from the decimal part.
  • TRUNC – Returns m truncated to n decimal places.
  • CEIL – Returns the largest integer greater than or equal to n.
  • FLOOR – Returns the smallest integer less than or equal to n.
  • MOD – Returns reminder of m divided by n
  • POWER – Returns m raised to the nth power.
  • SQRT – Returns square  root of n as real value.
  • ABS – Returns the absolute value of n.
  • SIGN – Returns the sign.
Miscellaneous Functions :
  • GREATEST – Used to pick highest values form the list of values.
  • LEAST – Used to pick lowest value from the list of values.
  • USER – Returns the current oracle users names with in the VARCHAR2 data type.
  • UID – It gives a number that identified the user since oracle is used in a multi-user environment.
  • USERENV – Returns information of varchar2 data type avove the current session.
  • VSIZE – Returns the number of bytes in the internal representaion of Expr.
  • SOUNDEX – Returns a character string containing the representaion of CHAR.
Advertisements