Oracle_sql (6) Single-line function

One, single-line function
1. Definition: A function that returns a value for each row of records when querying against a table or view.
2. Use: For SELECT statements, where conditions
3. Classification:
numeric function Number Functions
Character function (return character) Character Functions returning Character Values
Character function (return value) Character Functions returning number values
Date function DateTime Functions
Conversion function Conversion Functions
Other single-valued functions miscellaneous Single-row Functions

Second, numerical function
numeric function: A function that receives a numeric parameter that returns a numeric result.
Common functions:
1.MOD (m,n) m/n redundancy function
Select MOD (11,4) "modulus" from DUAL;
2.ABS (n) Take the absolute value function
Select ABS ( -15) "Absolute" from DUAL;
3.SIGN (n) symbolic function, positive return 1, negative return -1,0 return 0
Select sign ( -15) ' sign ' from DUAL;
4.CEIL (n) rounding up function
Select Ceil (15.7) "Ceiling" from DUAL;
5.FLOOR (n) down-rounding function
Select Floor (15.7) ' floor ' from DUAL;
6.ROUND (M)/round (m,n) rounding function
Select ROUND (15.193) "ROUND" from DUAL;
Select ROUND (15.193,1) "ROUND" from DUAL;
Select ROUND (15.193,-1) "ROUND" from DUAL;
7.TRUNC (M)/trunc (m,n) truncation function
Select TRUNC (15.79) "Truncate" from DUAL;
Select TRUNC (15.79,1) "Truncate" from DUAL;
Select TRUNC (15.79,-1) "Truncate" from DUAL;
Infrequently used functions:
1.POWER (M,n) m n-th square function
Select POWER (10,2) "raised" from DUAL;
2.SQRT square root function
Select SQRT (+) "Square root" from DUAL;
M-square root function of 3.LOG (M,N) n
Select log (10,100) "Log base of ten" from DUAL;
Select Log (10,power (10,2)) "Log base of ten" from DUAL;
M-square function of 4.EXP e (e = 2.71828183)
Select EXP (4) "E to the 4th power" from DUAL;
E (e = 2.71828183) quadratic root function of 5.LN m
Select LN (exp (4)) "Natural Log of exp (4)" from DUAL;
6.SIN Normal-Xuan function
Select SIN (3.14159265359/180) "Sine of Degrees" from DUAL;
7.COS Yu Xuan function
Select COS (3.14159265359/180) "cosine of degrees" from DUAL;
8.TAN tangent function
Select TAN (3.14159265359/180) "Tangent of Degrees" from DUAL;
9.ASIN sine function
Select ASIN (0.5) "Arc_sine" from DUAL;
Select ASIN (SIN (3.14159265359/180)) "Arc_sine" from DUAL;
10.ACOS Inverse Surplus Xuan function
Select ACOS (COS (3.14159265359/180)) "Arc_cosine" from DUAL;
11.ATAN Inverse tangent function
Select ATAN (TAN (3.14159265359/180)) "Arc_tangent" from DUAL;
12.atan2
Select ATAN2 (0.3, 0.2) "Arc_tangent2" from DUAL;
13.SINH Hyperbolic Zheng Xuan
Select SINH (1) "Hyperbolic sine of 1" from DUAL;
14.COSH Hyperbolic Yu Xuan function
Select COSH (0) "Hyperbolic cosine of 0" from DUAL;
15.TANH Hyperbolic tangent
Select TANH (. 5) "Hyperbolic tangent of. 5" from DUAL;

Three, character function (return character) Character Functions returning Character Values
1.CHR (n) converts a number to an ASCII code
Select CHR from dual;
2.CONCAT (m,n) connection string, equivalent to the join operator | |
Select CONCAT (' A ', ' B ') from dual;
Select CONCAT (CONCAT (' A ', ' B '), ' C ') from dual;
Select ' A ' | | ' B ' | | ' C ' from dual;
3.INITCAP (N) capitalize the first letter of a word in a character
Select Initcap (' Hello SQL ') "capitals" from DUAL;
4.UPPER (n) capitalize letters in characters
Select UPPER (' Hello SQL ') "uppercase" from DUAL;
5.LOWER (n) lowercase letters in characters
Select LOWER (' HELLO SQL ') "lowercase" from DUAL;
6.LPAD (C1,N,C2) The length of the string to be padded from the left
SELECT Lpad (' Page 1 ', 15, ' *. ') "Lpad example" from DUAL;
Select Lpad (' Page 1 ', "lpad example" from DUAL;
7.RPAD (C1,N,C2) The length of the string to be padded from the right
SELECT rpad (' Page 1 ', 15, ' *. ') "Lpad example" from DUAL;
Select Rpad (' Page 1 ', "lpad example" from DUAL;
8.TRIM white space or specified characters before and after trimming
Select Trim (' AB C ') "Trim Example" from dual;
--select trim (' 00098702348900 ', ' 0 ') "Trim Example" from dual;
Select Trim (0 from 00098702348900) "Trim Example" from dual;
9.LTRIM Trim the front-end spaces or specify characters
Select LTRIM (' AB C ') "LTRIM Example" from dual;
Select LTRIM (' 00098702348900 ', ' 0 ') "LTRIM Example" from dual;
10.RTRIM Trim the back end of a space or a specified character
Select RTRIM (' AB C ') "RTRIM Example" from dual;
Select RTRIM (' 00098702348900 ', ' 0 ') "RTRIM Example" from dual;
11.SUBSTR (c,p,l) intercepts substrings of a specified length from a specified position in a string
Select SUBSTR (' ABCDEFG ', 3,4) "Substring" from DUAL;
Select SUBSTR (' ABCDEFG ', 3) "Substring" from DUAL;
Select SUBSTR (' ABCDEFG ', -3) "Substring" from DUAL;
Select SUBSTR (' ABCDEFG ', -3,4) "Substring" from DUAL;
Select SUBSTR (' ABCDEFG ', +) "Substring" from DUAL;
Select SUBSTR (' ABCDEFG ', 0,2) "Substring" from DUAL;
Select SUBSTR (' I am a teacher ', 1,4) ' Substring ' from DUAL;
Select SUBSTRB (' I am a teacher ', 1,4) ' Substring ' from DUAL;
Select SUBSTRB (' I am a teacher ', 1,3) ' Substring ' from DUAL;
Select SUBSTRB (' A I B is C Old F Division G ', 1,3) "Substring" from DUAL;
Select SUBSTRC (' I am a teacher ', 1,3) ' Substring ' from DUAL;
Select SUBSTR (' A I B is C Old F Division G ', 1,3) "Substring" from DUAL;
Select SUBSTRC (' A I B is C Old F Division G ', 1,3) "Substring" from DUAL;

12.REPLACE (C,SC,RC) find SC substring from C string and replace with RC substring
Select REPLACE (' JACK and Jue ', ' J ', ' BL ') ' changes ' from DUAL;
SELECT REPLACE (' I am a teacher ', ' ', ' * ') from DUAL;
SELECT REPLACE (' I am a teacher ', ') from DUAL;
13.TRANSLATE (C,from_c,to_c) format conversion, convert string C according to From_c to To_c mode
SELECT TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
' 9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX ') "License" from DUAL;
SELECT TRANSLATE (' 2krw229 ', ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
' 0123456789 ') from DUAL;
Select TRANSLATE (' 2018 ', ' 0123456789 ', ' 0123456789 ') from dual;
Select TRANSLATE (' 1588 ', ' 0123456789 ', ' 0 one and three Woolu qi Ba Jiu ') from dual;

Four, character function (return value) Character Functions returning number values
1.ASCII (c) ASCII code to return characters
SELECT ASCII (' A ') from DUAL;
2.INSTR (string,substring,position,occurrence)
Find, returns the location of the substring
Select INSTR (' CORPORATE floor ', ' O ') "instring" from DUAL;
Select INSTR (' CORPORATE floor ', ' OR ') ' instring ' from DUAL;
Select INSTR (' CORPORATE floor ', ' OR ', 3) ' instring ' from DUAL;
Select INSTR (' CORPORATE floor ', ' OR ', -3) ' instring ' from DUAL; --When the positional parameter is negative, look from right to left.
Select INSTR (' CORPORATE floor ', ' OR ', 3,2) ' instring ' from DUAL;
Select INSTR (' CORPORATE floor ', ' OR ', -3,2) ' instring ' from DUAL;
Select Ename,instr (ename, ' O '), substr (Ename,instr (ename, ' O ')) from EMP

3.LENGTH (c) returns the string length
Select Length (' ABCDEFG ') "Length in characters" from DUAL;
Select Length (' I am a teacher ') "length in characters" from DUAL;
Select LENGTHB (' ABCDEFG ') "Length in bytes" from DUAL;
Select LENGTHB (' I am a teacher ') "Length in bytes" from DUAL;

V. Date function datetime Functions
Date +/-Number = Date
SELECT sysdate+5,sysdate-10 from DUAL;
Date-date = number
SELECT sysdate-to_date (' 2017-08-16 ', ' yyyy-mm-dd ') from DUAL;
1.SYSDATE Current date Systimestamp, Current_date,
Current_timestamp, Localtimestamp
Select Sysdate from dual;
Select Sysdate,systimestamp,current_date,
Current_timestamp,localtimestamp from dual;
2.last_day (date) Take the last 1 days of this month
Select Sysdate,last_day (sysdate) from dual;
3.next_day (date,n) Get the date of next week
Select Sysdate,next_day (sysdate,1) from dual;
4.add_months (d,n) month plus minus number
Select Sysdate,add_months (sysdate,1), Add_months (sysdate,-2),
Add_months (sysdate,0.5) from dual;
5.months_between (D1,D2) Month subtraction
Select Months_between (sysdate,to_date (' 2017-08-16 ', ' Yyyy-mm-dd ')) "MONTHS" from DUAL;
6.EXTRACT (type from date) is extracted from the date of the month and the day seconds
SELECT EXTRACT (Year from DATE ' 2017-03-07 ') from DUAL;
SELECT EXTRACT (MONTH from sysdate) from DUAL;
SELECT EXTRACT (Day from sysdate) from DUAL;
SELECT EXTRACT (HOUR from Localtimestamp) from DUAL;
SELECT EXTRACT (MINUTE from Localtimestamp) from DUAL;
SELECT EXTRACT (SECOND from Localtimestamp) from DUAL;
7.TRUNC (date) Date truncation
Select TRUNC (sysdate, ' yyyy ') "New year" from DUAL;
Select TRUNC (sysdate, ' mm ') "New Months" from DUAL;
Select TRUNC (sysdate, ' DD ') "New Day" from DUAL;
Select TRUNC (sysdate, ' hh24 ') "New Hour" from DUAL;
Select TRUNC (sysdate, ' mi ') "New Minute" from DUAL;
8.ROUND (date) Date rounding
Select ROUND (sysdate, ' yyyy ') "New year" from DUAL;
Select ROUND (sysdate, ' mm ') "New year" from DUAL;
Select ROUND (sysdate, ' DD ') "New year" from DUAL;
Select ROUND (sysdate, ' hh24 ') "New year" from DUAL;
Select ROUND (sysdate, ' mi ') "New year" from DUAL;

Six, conversion function Conversion Functions
Conversion function: Converts a numeric value from one type to another.
1.bin_to_num Binary to Digital
SELECT Bin_to_num (1,0,1,0) from DUAL;
2.to_char (datetime) Date-to-string
SELECT to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;
3.to_date String to date
SELECT sysdate-to_date (' 2017-01-01 ', ' YYYY-MM-DD ') from dual;
4.to_number characters to numbers
SELECT to_number (' 012345 ') from dual;
SELECT to_number (' 0123.45 ', ' 9999d99 ') from dual;
SELECT to_number (' $0123.45 ', ' $9999d99 ') from dual;

Vii. other single-valued functions miscellaneous Single-row Functions
1.DECODE Conditional Selection function
SELECT Emp.*,decode (deptno,10, ' ACCOUNTING ',
(a), ' the ', ' ['], ' ' SALES ', ' OPERATIONS ') from EMP;
SELECT Emp.*,decode (sal-1600),
1, ' wages are greater than 1600 ', 0, ' wages equal to 1600 ',-1, ' wages less than ') from EMP;
SELECT Emp.*,decode (sign (InStr (ename, ' A ')),
1, ' name contains a ', 0, ' The name does not contain a ') from EMP;
2.coalesce (Expr1, Expr2, ..., exprn) returns the first non-empty expression
SELECT Emp.*,coalesce (comm,0) from EMP;
3.NVL (c,v) NVL2 (C,V1,V2) NULL conversion
SELECT EMP.*,SAL+COMM,SAL+NVL (comm,0) from EMP;
SELECT Emp.*,nvl2 (comm,sal+comm,sal) from EMP;
4.USER returns the current user name \id
SELECT USER from dual;
SELECT UID from DUAL;
5.sys_guid returns a globally unique identifier (the original value) that consists of 16 bytes.
SELECT Sys_guid () from dual;
6.NULLIF (S1,S2) s1 returns null when equal to S2, returns S1 when unequal
SELECT Emp.deptno,dept.deptno,
Nullif (Emp.deptno,dept.deptno) from emp,dept;
7.GREATEST returns the maximum value in the collection
Select Greatest (' A ', ' B ', ' C ') "greatest" from DUAL;
8.LEAST returns the minimum value in the collection
Select LEAST (' A ', ' B ', ' C ') "LEAST" from DUAL;

Oracle_sql (6) Single-line function