%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%% numerical function: ABS (M) M absolute value mod (m, n) m is rounded by the remaining Power (M, N) M after N, round (M [N]) M The value of N bits after the decimal point (N default is 0) Trunc (m [, n]) m truncated N-bit digits (N default 0) %%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Character function: Initcap (ST) Returns ST returns St. All other letters lowercase LOWER (ST) returns all the letters of each word to st) all the words all write all the words all the words CONCAT (ST1, ST2). For the end of ST2, the LPAD (ST1, N [| ") LPAD (ST1, N [|") LPAD (ST1, N [, ST2]) returns to the rightmost ST, and ST is filled with ST2 in the left side of ST1 until the default of the length N, ST2 is Space RPAD (ST1, N [, ST2]) returns the left aligned ST, ST is filled with ST2 in ST2 until the default length of N, ST2 is a space LTRIM (ST [SET]) returns ST, ST is Delete the set from the left to the first one is not a character in the SET. By default, the space RTRIM (ST [SET]) returns St. ST is the character in the SET from the right until the first one is not a set. By default, the space Replace (st, search_st [, replace_st]) will replace the search_st of ST_ST each time in ST, returns a ST. Under default, Search_STSUBSTR (ST, M [, N]) n = Returns the ST string, starting from the M position, taking N characters long.
When the default, return to the ST end length (ST) value, returning the character number INSTR in ST (ST1, ST2 [, M, N]]) value, return ST1 starting from the Mth character, ST2 Nth appears The location, the default value of M and N is 1 case: 1.Select Initcap ('Thomas'), INITCAP ('Thomas') from test; Initca INITCA ------ ---- Thomas Thomas2. SELECT Concat ('ABC', 'DEF') "First" from test; first ----- abcdef3.select 'ABC' || '' || 'DEF' "first" from test; first ----- ABC Def4.Select LPad (Name, 10), RPAD (Name, 5, '*') from test; lpad (name, 10) RPAD (Name, 5, '*') ---------- - ---------------- MMX mmx ** Abcdef abcde5. Remove points and words St and Rdselect RTRIM (Address, '. St rd') from test6.select Name, Replace (Name, 'A', '*') from test; name, 'a', '*') -------------------- ----- Great Gre * T7.Select Substr ('Archibald Bearisol', 6, 9) A, Substr ('Archibald Bearisol', 11) B from test; A b ---------- --- Bald Bear Bearisol8.Select Name, INSTR (Name, '') A, INSTR (Name, '', 1, 2) b from test; Name A b ------------ - --------- Li Lei 3 0L IL 2 4 %%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%% Conversion Function: NVL (M, N) If the M value is null, return n, otherwise return MTO_CHAR (M [, FMT]) M converted from a value to the specified format string FMT default, the width of the FMT value is only able to accommodate all valid digital TO_NUMBER (ST [FMT]) ST from the characteristic data. According to the value of the specified format, the size of the default time value format string is just the entire number: to_char () function format: --------------------- ---------- Symbol Description -------------------------------- 9 Each 9 representative One digit 0 represents the pilot 0 $ dollar sign to be displayed Print any local currency symbol on the left of the number. Print the decimal decimal point, print a comma representing the thousandity ---------------------------- ----------------------- Example: 1.Select TO_NUMBER ('123.45') TO_NUMBER ('234.56') Form Test; TO_NUMBER ('123.45') TO_NUMBER ('234.56'
---------------------------------------- 358.012.select to_char (987654321) from test ; TO_CHAR (987654321) ------------------ 9876543213.select to_char (123, '$ 9,999,999) a, to_char (54321,' $ 9,999,999) B, to_CHAR (9874321, '$ 9,999,999') c from test; a b c ---------------- --------- $ 123 $ 54,321 $ 9,874,3214.select to_char (1234.1234, '999, 999.999') a, to_char (0.4567, '999, 999.999) b, to_char (1.1,' 999, 999.999 ') from test; a b c ------------------ ----------- 1,234.123 .457 1.100 %%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%% Group function: AVG ([Distinct / All] n) column N The average value count ([all] *) Returns within the query The number includes row max ([DistINCT / All] n) of the non-null value of the repetition value and null value count ([DistINCT / All] n) This column or expression is MIN ([Distinct / All] n). Or the minimum stdev ([Distinct / All] n) This column or the standard deviation of the expression, ignores null value SUM ([Distinct / All] n) This column or expression of the sum of Variance ([Distinct / All] n) The column or expression variance, ignore the null value %%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%% Date function: add_months (d, n) Date D plus N month Last_Day (D) contains the date of the last day of the month Month_between (D, e) Date D and E Month, e prior to DNEW_TIME (D, A, B) A time zone date and time D Date and time next to the B time zone NEXT_DAY (D, day) is date than Date DAY, specified by day The date of the date Sysdate Current system date and time Greatest (D1, D2, ... DN) give the last date Least (D1, K2, ... DN) in the date list in the date in the date list. Date TO_CHAR (D [, FMT]) Date D Press the format specified by the FMT to transition into a string to_date (ST [FMT]) string ST is transferred to the date value specified by the FMT, if FMT is ignored, St To use default Format Round (D [, FMT]) Date D Press FMT to specify format round to the nearest date Trunc (D [, fmt]) Date D Press FMT to specify the format to cut off to the nearest date: Date format: ----- --------------------------- Format code Description Example or the range of values -
------------------------------ DD This day 1-3DY three uppercase letters said. ..SATDAY complete week, uppercase English Sunday, ... Saturdaymm 1-12MON three uppercase letters represent the month jan, ... Decmonth complete january, ... decamberrm Rome I, ... Xiiyy Or YYYY two digits, four digits year hh: mi: ss: score: second HH12 or HH24 Display MI SS second AM or PM on an afternoon at 12 hours or 24 hours, the SP suffix SP requires spelling any numerical field TH The number of the suffix th indicates the number of orders 4th, 1stfm prefix on the month or day or annual value, prohibited from filled ---------------------------------------------------------------------------------------------------------------------------- ----- Example: 1. Date of the next Friday Select Next_day (sysdate, 6) from test; 2. Today's date SELECT Add_Months (Sysdate, -2) from test; %%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % NULL Value Processing Function NVL (EXPR1, EXPR2) If EXPR1 is NULL, return EXPR2NVL2 (Expr1, Expr2, Expr3) If EXPR1 is non-empty, return EXPR2, if expr1 is null, return expr3nullif (expr1, expr2) if EXPR1 = EXPR2, return null, otherwise returns EXPR1COALESCE (EXPR1, EXPR2, ..., EXPRN) Returns the first non-empty expression %%%%%%%%%%%%%%%%% of the expression list %%%%%%%%%%%%%%%%%%%%%%%%%%%% Condition Expression, Case Expression or Decode Conditions Logic Case Expression Case Expr When Comparison_Expr1 Then Return_EXPR1 [WHEN COMPA RISON_EXPR2 THEN RETURN_EXPR2 WHEN COMPARISON_EXPRN THEN RETURN_EXPRN ELSE ELSE_EXPR] END
Decode function decode (Col | Expression, Search1, Result1 [, Search2, Result2, ...,] [, Default] example: 1.sql> Select Sid, Serial #, Username, Decode (Command, 2 0, 'None ', 3 2,' INSERT ', 4 3, 5' SELECT ', 6 6 6,' UPDATE ', 7 7,' DELETE ', 8 8,' Drop ', 9' Other ') CMD from V $ Session Where Type ! = 'Background'; SID Serial # Username CMD ------------------------------------- -------------- 1 1 None 2 1 None 3 1 None 4 1 None 5 1 None 6 1 None 7 1275 None 8 1275 None 9 20 Gao SELECT 10 40 GAO NONE
