Common Oracle function summary ********************

Common Oracle function summary ******************** **************************************** *************************************** * SQL group functions (Num can be a column or expression )* (Null values are ignored, default between distinct and all is all )* **************************************** *************************************** AVG ([distinct or all] num) -- average value Count (distinct or all] num) -- number of values Max ([distinct or all] num) -- maximum value Max ([distinct or all] num) -- Minimum value Stddev ([distinct or all] num) -- Standard Deviation Sum ([distinct or all] num) -- sum of Values Variance ([distinct or all] num) -- Variance of Values **************************************** *************************************** * Miscellaneaous functions :* **************************************** *************************************** Decode (expr, srch1, return1 [, srch2, return2. ..], default] -- If no search matches the expression then the default is returned, -- Otherwise, the first search that matches will cause -- The corresponding return value to be returned Dump (column_name [, FMT [, start_pos [, length]) -- Returns an internal Oracle format, used for getting info about a column -- Format options: 8 = octal, 10 = decimel, 16 = hex, 17 = characters -- Return type codes: 1 = varchar2, 2 = number, 8 = long, 12 = date, -- 23 = raw, 24 = long raw, 69 = rowid, 96 = char, 106 = mlslabel Greatest (expr [, expr2 [, expr3. ..] -- Returns the largest value of all expressions Least (expr [, expr2 [, expr3. ..] -- Returns the smallest value of all expressions Nvl (expr1, expr2 -- If expr1 is not null, it is returned, otherwise expr2 is returned Sqlcode -- Returns SQL error code of last error. Can not be used directly in query, -- Value must be set to local variable first Sqlerrm -- Returns SQL error message of last error. Can not be used directly in query, -- Value must be set to local variable first UID -- Returns the user ID of the user you are logged on -- Useful in selecting information from low level sys tables User -- Returns the user name of the user you are logged on Userenv ('option ') -- Returns information about the user you are logged on -- Options: entryid, sessionid, terminal, language, label, osdba -- (All options not available in all Oracle versions) Vsize (expr) -- Returns the number of bytes used by the expression -- Useful in selecting information about table space requirements **************************************** *************************************** * SQL date functions (DT represents Oracle Date and Time )* * (Functions return an oracle date unless otherwise specified )* **************************************** *************************************** Add_months (DT, num) -- adds num months to DT (Num can be negative) Last_day (DT) -- last day of month in month containing dt Months_between (dt1, dt2) -- returns fractional value of months between dt1, dt2 New_time (DT, tz1, tz2) -- dt = date in Time Zone 1, returns date in Time Zone 2 Next_day (DT, STR) -- Date of first (STR) after DT (STR = 'monday', Etc ..) Sysdate -- present system date Round (dt [, FMT] -- rounds dT as specified by format FMT Trunc (dt [, FMT] -- truncates dT as specified by format FMT **************************************** *************************************** * Number functions :* **************************************** *************************************** ABS (Num) -- absolute value of num Ceil (Num) -- smallest integer> OR = num Cos (Num) -- cosine (Num), num in radians Cosh (Num) -- hyperbolic cosine (Num) Exp (Num) -- e raised to the num power Floor (Num) -- Largest Integer <OR = num Ln (Num) -- natural logarithm of num Log (num2, num1) -- logarithm base num2 of num1 MoD (num2, num1) -- remainder of num2/num1 Power (num2, num1) -- num2 raised to the num1 power Round (num1 [, num2] -- num1 rounded to num2 decimel places (default 0) Sign (Num) -- sign of num * 1, 0 if num = 0 Sin (Num) -- sin (Num), num in radians Sinh (Num) -- hyperbolic sine (Num) SQRT (Num) -- square root of num Tan (Num) -- tangent (Num), num in radians Tanh (Num) -- hyperbolic tangent (Num) Trunc (num1 [, num2] -- truncate num1 to num2 decimel places (default 0) **************************************** *************************************** * String functions, string result :* **************************************** *************************************** (Num) -- ASCII character for num CHR (Num) -- ASCII character for num Concat (str1, str2) -- str1 concatenated with str2 (same as str1 | str2) Initcap (STR) -- capitalize first letter of each word in Str Lower (STR) -- STR with all letters in lowercase Lpad (str1, num [, str2]) -- left pad str1 to length num with str2 (default spaces) Ltrim (STR [, set]) -- remove set from left side of STR (default spaces) Nls_initcap (STR [, nls_val]) -- same as initcap for different ages Nls_lower (STR [, nls_val]) -- same as lower for different ages Replace (str1, str2 [, str3]) -- replaces str2 with str3 in str1 -- Deletes str2 from str1 if str3 is omitted Rpad (str1, num [, str2]) -- right pad str1 to length num with str2 (default spaces) Rtrim (STR [, set]) -- remove set from right side of STR (default spaces) Soundex (STR) -- phonetic representation of STR Substr (STR, num2 [, num1]) -- substring of STR, starting with num2, -- Num1 characters (to end of STR if num1 is omitted) Substrb (STR, num2 [, num1]) -- same as substr but num1, num2 expressed in bytes Translate (STR, set1, set2) -- replaces set1 in STR with set2 -- If set2 is longer than set1, it will be truncated Upper (STR) -- STR with all letters in uppercase **************************************** *************************************** * String functions, numeric result :* **************************************** *************************************** ASCII (STR) -- ASCII value of STR Instr (str1, str2 [, num1 [, num2]) -- position of num2th occurrence -- Str2 in str1, starting at num1 -- (Num1, num2 default to 1) Using B (str1, str2 [, num1 [num2]) -- same as instr, byte values for num1, num2 Length (STR) -- number of characters in Str Lengthb (STR) -- number of bytes in Str Nlssort (STR [, nls_val]) -- nls_val byte value of STR **************************************** *************************************** * SQL conversion functions * **************************************** *************************************** Chartorowid (STR) -- converts STR to rowid Convert (STR, chr_set2 [, chr_set1]) -- converts STR to chr_set2 -- Chr_set1 default is the datbase Character Set Hextoraw (STR) -- converts hex string value to internal raw values Rawtohex (raw_val) -- converts raw HEX value to hex string value Rowidtochar (rowid) -- converts rowid to 18 character string format To_char (expr [, FMT]) -- converts expr (date or number) to format specified by FMT To_date (STR [, FMT]) -- converts string to date To_multi_byte (STR) -- converts single byte string to multi byte string To_number (STR [, FMT]) -- converts STR to a number formatted by FMT To_single_byte (STR) -- converts multi byte string to single byte string **************************************** *************************************** * SQL date formats * **************************************** *************************************** BC, B .C. BC indicator AD, A.D. Ad indicator CC, SCC century code (SCC between des space or-sign) Yyyy, syyyy 4 digit year (syyyy between des space or-sign) Iyyy 4 digit ISO year Y, YYY 4 digit year with comma YYY, YY, or y last 3, 2, or 1 digit of year Year, syear year spelled out (syear includes space or-sign) Rr last 2 digits of year in prior or next century Q quarter or year, 1 to 4 Mm Month-from 01 to 12 Month spelled out Mon month 3 letter abbreviation Rm Roman numeral for month WW week of year, 1 to 53 Iw iso week of year, 1 to 52 or 1 to 53 W week of month, 1 to 5 (Week 1 begins 1st day of the month) D day of week, 1 to 7 Dd Day of month, 1 to 31 Ddd day of year, one to 366 Day of week spelled out, nine characters right padded Dy day abbreviation J # Of days since Jan 1, 4712 BC HH, hh12 hour of day, 1 to 12 Hh24 hour of day, 0 to 23 Mi minute of hour, 0 to 59 SS second of minute, 0 to 59 Sssss seconds past midnight, 0-86399 Am, A.M. Am indicator PM, P. M. PM indicator Any puctuation punctuation between format items, as in 'dd/MM/yy' Any text between format items Th converts 1 to '1st', 2 to '2nd ', and so on SP converts 1 to 'one', 2 to 'two', and so on Spth converts 1 to 'first', 2 to 'second', and so on FX fill exact: Uses exact pattern matching FM fill mode: toggles suppression of blanks in output **************************************** ***************************************