Listen Software’s How To: Oracle Functions

Oracle String Functions

  • initcap(char)–Returns a char with the first
    letter of each word in uppercase
  • instr(char1, char2,n,m)–n=position m=occurance
    returns the numeric position of char2 in char1
  • length(char)–Length of char
  • lower(char)–Returns char
    with all lower case
  • lpad(char1,n,char2)–Return a char
    with char1 left pad with char2
  • ltrim(char)–Remove all leading spaces
  • rpad(char1,n,char2)–Returns char
    with char1 right pad with char2
  • rtrim(char)–Remove all trailing spaces
  • substr(char,m,n)–Returns substring
    start at m for n characters
  • upper(char)–Converts all characters
    to uppercase
  • chr(n)–Returns the ascii code equivalent
  • concat(char1, char2)–Return a char
    which is the result of char1 and char2

Oracle Time Functions

  • ADD_MONTHS(d,n)–Add n months to d date
  • LAST_DAY(d)–Returns last day of the month
  • MONTHS_BETWEEN(d1,d2)–Difference between d1 and d2
  • ROUND(d,fmt)–Round to format
  • TO_CHAR(string,format)
  • TO_DATE(string,format)

Oracle Number Functions

  • ABS(value)–Absolute value
  • CEIL(value)–Smallest integer larger than equal to value
  • COS(value)–Cosine value
  • EXP(value)–e raise to value
  • FLOOR(value)–Largest value smaller than equal to value
  • ROUND(value)–Rounding of value to precision
  • SIGN(value)–1 if positive -1 if negative
  • SQRT(value)–Square root of value
  • TAN(value)–Tangent of value
  • TRUNC(value)–Value truncated to precision

Oracle Group Functions

  • Avg(value)–Average of value for a group of rows
  • Count(value)–Count rows of columns
  • Max(value)–Maximum of all value for groups of rows
  • Min(value)–Minimum of all values for groups of rows
  • Sum(value)–Sum of all values for groups of rows

Latest Articles