Title Oracle Common Function Highlights HBZXF [Original] Keyword Oracle Common Function Collection
Value function:
Absolute value of ABS (M) M
MOD (M, N) M is after the remainder
N times of Power (M, N) M
Round (M [, N]) M is rounded to the value of N bit after the decimal point (n default is 0)
Trunc (M [, N]) m truncates the value of N-bit digits (n default is 0)
Character function:
Initcap (ST) returns St Writing the first letters of each word, all other letters lowercase
Lower (ST) Returns ST to write all the letters of each word
Upper (ST) Returns ST to write all the letters of each word
Concat (ST1, ST2) Returns the end of ST2 to ST1 (available operator || ")
LPAD (ST1, N [, ST2]) returns to the rightmost ST, ST is filled with ST2 in the left side of ST1 until the default space of the length n, ST2 is spaced.
RPAD (ST1, N [, ST2]) returns the left aligned ST, ST is filled with ST2 in the right side of ST1 until the default space of the length n, ST2 is spaced.
LTRIM (ST [SET]) Returns St. St. The character is deleted from the left until the first one is not a set. By default, it is a space.
RTRIM (ST [SET]) Returns St. ST is a character in the SET from the right until the first one is not a set. By default, it is a space.
Replace (ST, Search_st [, Replace_St]) will replace each time SEARCH_ST replace with Replace_ST, returns a ST. Delete Search_ST when default
SubStr (ST, M [, N]) n = Returns the substring of the ST string, starting from the M position, taking N characters long. Over the default, return to the end of the ST
LENGTH (ST) value, return the number of characters in ST
Instr (ST1, ST2 [, M [, N]]) value, returns ST1 starting from the Mth character, the position of the nth occurrence of ST2, the default value of M and N is 1
example:
1.
SELECT INITCAP ('Thomas'), INITCAP ('Thomas') from test;
INITCA INITCA
------ ------
Thomas thomas
2.
Select Concat ('ABC', 'DEF') "First" from test;
First
-----
ABCDEF
3.
SELECT 'ABC' || '' || 'DEF' "First" from test;
First
-----
ABC DEF
4.
Select LPAD (Name, 10), RPAD (Name, 5, '*') from test;
LPAD (Name, 10) RPAD (Name, 5, '*')
------------ ----------------
MMX MMX **
Abcdef abcde
5.
Remove points and words ST and RD at the end of the address field
SELECT RTRIM (Address, '. st rd') from test
6.
Select Name, Replace (Name, 'A', '*') from test;
Name Replace (Name, 'A', '*')
---- ---------------------
Great greet
7.
Select Substr ('Archibald Bearisol', 6, 9) A, Substr ('Archibald Bearisol', 11) b from test; a b
------- -------
Bald Bear Bearisol
8.
Select Name, INSTR (Name, '') A, INSTR (Name, ', 1, 2) b from test;
Name a b
-----------------------
Li Lei 3 0
L i l 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 average
COUNT ([all] *) Returns the number of rows within the query includes repetition values and null values
COUNT ([Distinct / All] n) Non Null Value
Max ([DistINCT / All] n) This column or expression is maximum
min ([Distinct / All] n) This column or expression minimum
Stdev ([Distinct / All] n) This column or expression standard deviation, ignore null value
SUM ([Distinct / All] n) This column or expression of the sum
Variance ([DISTINCT / ALL] N) This column or expression variance, ignore 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 Moon number, E first in D
New_time (d, a, b) a time zone date and time D Date and time
Next_day (d, day) is better than the date D, the date specified by day
Sysdate Current System Date and Time
The last date in the date list given by GreateSt (D1, D2, ... DN)
The earliest date in the date given by LEAST (D1, K2, ... DN)
TO_CHAR (D [, FMT]) Date D Transitions to strings in the format specified by FMT
TO_DATE (ST [FMT]) String ST is transferred to the date value specified by the FMT, if FMT is ignored, St To use the default format
Round (D [D [FMT]) Date D Press FMT to specify the format to the nearest date
Trunc (D [, FMT]) Date D Press FMT to specify the format to cut to the nearest date
Attachment:
Date format:
--------------------------------
Format code Description Example or can be valued
--------------------------------
DD this month 1-3
DY three uppercase letters said with a few Sun, ... SAT
Day complete week, uppercase English Sunday, ... Saturday
MM month 1-12
Mono three uppercase letters indicated by Jan, ... DEC
Month complete january, ... decEmber
RM Number I, ... XII
Two digits, four digits of YY or YYYY
HH: MI: SS Time: Score: Second
HH12 or HH24 is displayed in 12 hours or 24 hours
MI points
SS seconds
AM or PM on the afternoon indicator
SP suffix SP requires spelling any numerical field
The TH suffix th indicates that the added number is the number of orders 4th, 1st
FM prefix to the month or day or year, prohibited fill
---------------------------------example:
1.
Next Friday date
SELECT NEXT_DAY (Sysdate, 6) from test;
2.
Today's date two months ago
SELECT add_months (sysdate, -2) from test;
