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 will each word The first letter uppercase, all other letters lowercase LOWER (ST) Return to ST Return all the letters of each word Returns all the words all override all the upper case CONCAT (ST1, ST2) Returns ST for ST2 ST1 At the end (available operators "||") LPAD (ST1, N [, ST2]) returned to the rightmost ST, ST is filled with ST2 at the left side of ST1 until the default length of 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 default, and ST is returned to ST, and ST is deleted from the left. Character until the first one is not a character. 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 i L 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 -
