*********************************************************** **************************** * 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 = varcha2, 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 (e r s s s *********************************************************** ** * SQL DATE FUNCTIONS (DT Repens 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 [, Format FMT Trunc (DT [DT [DT [t] - Truncates DT As Specified by Format FMT ********************** *********************************************************** ****** * Number functions: * ************************************************* ************************************* ABS (NUM) - ABSOLUTE VALUE OF NUM CEIL (NUM) - Smallst 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 * ********************************************************** ***************************** (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 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 RTRIM (Str [SET]) - Remove Set from Right Side of Str (Default Spaces) SOU NDEX (STR) - Phonetic Reperestation 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 Belftes Translate (STR, SET1, SET2) - Replace Set1 in Str with Set2 - IN STR WIGER 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 spec specific 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 " Formatted by fmt to_single_byte (str) - Converts Multi Byte String to Single Byte String ************************* *********************************************************** ****** * SQL DATE FORMATS * ******************************************************** **************************************** BC, BC BC Indicator AD, AD Ad Indicator CC, SCC CENTURY CODE (SCC Includes Space Or - Sign) YYYY, SYYYY 4 DIGIT YEAR (SYYY INCLUDES SPAYY 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 CENTUR 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 Day Day Jan 1, 4712 BC HH, HH12 HOUR Of Day, 1 To 12 HH24 HOR OF DAY , 0 to 23 Mi Minute of Minute, 0 to 59 SSSSS SECONDS PAST MIDNIGHT, 0 to 86399 AM, AM AM Indicator PM, PM PM Indicator Any Puctuation Punctuation Between Format Items,
