Summary of SQL functions

numeric functions
ABS Take absolute value
POWER exponentiation
LN 10 is the base power
SQRT Square Root
The n-th-exponent of EXP E
LOG (M,n) m is the base n exponentiation
Mathematical operation function: ACOS ATAN ATAN2 COS COSH sign SIN SINH TAN TANH
Ceil is greater than or equal to taking an integer
Floor is less than or equal to taking an integer
MOD take remainder
ROUND (n,m) rounding value by the number of digits of M if ROUND (date): Noon 12 will be tomorrow's date. Round (sysdate, ' Y ') is the first day of the year
TRUNC (n,m) by the number of digits of M to take the previous value if the TRUNC (date), the real province is to remove the time
Character functions
CHR returns characters by number in the character set of the database
CONCAT (C1,C2) combines two characters c1,c2 into one character, and | | Same
Replace (C,S,R) replaces the character C that appears s with R, returning the new character
SUBSTR (C,m,n) m is greater than 0, character C takes n characters from the front m, m equals 0 and 1, M is small with 0, character C takes n characters from behind M

TRANSLATE (C,F1,T1) character C convert to a new string by F1 to T1 rule
Initcap characters first uppercase, other characters lowercase
LOWER characters All lowercase
UPPER characters all uppercase
LTRIM (C1,C2) Removes the character that appears on the left side of the character C1 C2
RTRIM (C1,C2) Removes the character that appears to the right of the character C1 C2
TRIM (C1,C2) removes characters C1 the left and right sides of the character C2
The Lpad (C1,N,C2) character C1 is displayed by the specified number of digits n, and the insufficient number replaces the left empty space with the C2 string
The Rpad (C1,N,C2) character C1 is displayed by the specified number of digits n, and the insufficient digits replace the right empty space with the C2 string
Tip: Remove the space in the middle of the string to use replace
Date Function
Add_months (d,n) Date value plus n month
Last_day (d) Returns the date of the last day of the month
Months_between (D1,D2) month between two date values
Next_day (d) Return date value next day
Sysdate the current system time
Dual is the SYS user's next empty table, which has only one field dummy
Tip: Dual can be interpreted as a virtual table and is used when querying system function return values to satisfy the SELECT syntax rules, such as select Sysdate from dual; Select 3 + 4 from dual;
Conversion Functions
To_char (date, ' dates display format ')
To_char (number) is used for display or format alignment of the report
To_date (char, ' date display format ')
To_lob Convert a Long field to LOB field
To_number (char) for calculating or comparing sizes
Date display Format
Years: YYYY, year, YY
Season: Q
Month: MM, month, MON
Days: DD, Day, DY
When: HH24, HH12, HH (12 hours)
Score: MI
Seconds: SS
Logical comparison function
NVL (EXPR1, EXPR2)
When EXPR1 is empty, replace with EXPR2
DECODE (Expr,v1,r1,v2,r2 ...)
Return R1 when EXPR=V1
When Expr=v2 is put back V2 ...
After expr, the condition and return values appear in pairs, and the last single value is the return value that is not in the condition, and returns null if there is no single value that does not satisfy the condition.
SELECT ename, DECODE (JOB, ' manager ', ' managers ', ' president ', ' President ', ' salesman ', ' Sales ', ' unknown ') from SCOTT. EMP;
Case if condition then Express1 ELSE express2 END
When condition is established return EXPRESS1 otherwise return EXPRESS2
SELECT ename, HireDate,
(Case if To_number (To_char (hiredate, ' MM ')) > 6 Then ' second half ' of ' ELSE ' END) As the entry time
From SCOTT. EMP;
--to_char (hiredate, ' MM ') Take time month

Summary of SQL functions