Numerical function: The absolute value of the abs (M) m mod (M, N) m is rounded from the remaining Power (M, N) M after N, and the N-bit re-n-bit re-decimal The value (n default is 0) trunc (m [, n]) m trunks the value of N-bit digits (n default is 0)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Character function: initCap (ST) Returns ST to write the first letter of each word, all other letters lowercase LOWER (ST) Return to ST to returns all the letters of each word Return to ST to all the words of each word Concat (ST1, ST2) Returns ST to the end of ST2 (available operator "||") LPAD (ST1, N [| ") LPAD (ST1, N [, ST2]) returns to the rightmost ST, ST is filled with ST2 in the left side of ST1 until the length The default for N, ST2 is space RPAD (ST1, N [, ST2]) returns the left aligned ST, and ST is filled with ST2 in the right side of ST1 until the default to the length of N, ST2 is space LTRIM (ST [, SET] Returns St, State to delete the set from the left until the first one is not a set 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 to convert the width of the FMT value when converted from one value to the specified format, the width of the FMT value Receiving all valid digital to_number (ST [, FMT]) ST is converted from the characteristic data into values in the specified format, the size of the value of the value is just the format of the TO_CHAR () function: --- ------------------------------Symbol Description------------------ -------------- 9 Each 9 represents a number 0 represents the pilot 0 $ dollar symbol to display Print any local currency symbol on the left of the number. Print decimal The decimal point, print represents a comma ------------------------------- 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 the number of rows within the query includes duplicate values and null values count ([Distinct / all] n) non-null value RV Max ([Distinct / All] N) This column or expression of maximal min ([DistINCT / All] n) This column or expression of the minimum stdev ([Distinct / all] n) This column or expression Standard deviation, ignore null sum ([DistINCT / All] n) This column or expression of the sum of Variance ([Distinct / All] n) This column or expression of the variance, ignore null values
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
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 Moon number, e prior DNEW_TIME (D , A, B) A time zone Date and time D Date_day (d, day) than the date D night D During Date Date, the date of the date specified by Day Sysdate Current System Date and Time Greatest (D1, D2) , ... DN) gives the last date Least (D1, K2, ... DN) given the earliest date to_char (D [, fmt]) Date D in the date D Dated in the format specified by FMT Transition into a string to_date (ST [FMT]) string ST is transferred to the date value specified by FMT, if FMT is ignored, St To use the default format Round (D [D [, FMT]) Date D Press FMT to specify format Enter the most recent date trunc (d [, fmt]) Date D Download the FMT to specify the format to cut to the nearest date: Date format: ------------------- ----------- Format code Description Example or the range of values --------------------------- --- DD this month 1 1-3DY three uppercase letters expressed as a few Sun, ... Satday complete week, uppercase English Sunday, ... Saturdaymm 1-12MON three uppercase letters represent the month Jan , ... Decmonth's full january, ... decemrrm month Rome I, ... Xiiyy or YYYY two, four digits year hh: mi: ss: 分: HH12 or HH24 in 12 hours or 24 hours Display MI SS Second AM or PM On afternoon indicator SP suffix SP requires spelling Any numeric field TH suffix th indicated that the added number is the number of orders 4th, 1stfm prefix on the month or day or annual value, prohibited from filling ---------- ---------------------------example:
