******************************************************************************* * 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 as       -- useful in selecting information from low level sys tables USER       -- returns the user name of the user you are logged on as USERENV('option')       -- returns information about the user you are logged on as       -- 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 languages NLS_LOWER(str [,nls_val])   -- same as lower for different languages 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 of                                    -- str2 in str1, starting at num1                                    -- (num1, num2 default to 1) INSTRB(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 includes space or - sign) YYYY, SYYYY     4 digit year (SYYYY includes 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           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, 1 to 366 DAY             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 to 86399 AM, A.M.        am indicator PM, P.M.        pm indicator any puctuation  punctuation between format items, as in 'DD/MM/YY' any text        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