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