It says that ORCL's basic SQL functions are in this diagram:
So, I'm here. These functions are all knocked aside:
☆sql function
--One. Character functions
--1. Casing control functions
Select lower (' KK ') from dual--capitalization converted to lowercase
Select Upper (' DJJDFJDJD ') from dual--lowercase converted to uppercase
Select Initcap (' Dadfasdfsdfaa ') from dual--capitalize first letter
--2. Character control functions
--2.1. Stitching
Select Concat (' Happy ', ' boy ') from dual
--2.2.substr (' String to intercept ', starting position)
Select substr (' Happyboy ', ' 5 ') from dual
--2.3.substr (' character to intercept, start position, take a few characters ')
Select substr (' Happyboy ', 2,3) from dual
--2.4.length (' string '): Number of characters counted
Select Length (' Happy 1z ') from dual
--2.5.LENGTHB (' string '): Number of bytes counted
Select LENGTHB (N ' Happy 1z ') from dual
--2.6.instr (' Large string ', ' small string ') returns the position of a small string in a large string
--2.6.1. The position of the second ' OR ' from left to right
Select InStr (' CORPORATE floor ', ' OR ', 3,2) from dual
--2.6.2. Read the position of the left-to-right (second ' OR ' from right to left)
Select InStr (' CORPORATE floor ', ' OR ', -3,2) from dual
--2.6.3. read from left to right byte is the position of ' OR '
Select INSTRB (' CORPORATE floor ', ' OR ', 5,2) from dual
--2.7.lpad () and Rpad ()
--2.7.1. Fill the ' * ' to the left margin
Select Lpad (' Happy ', ten, ' * ') from dual
--2.7.2. Fill the ' * ' to the right margin
Select Rpad (' Happy ', ten, ' * ') from dual
--2.8.trim ()
--delete ' a ' around ' ahappy '
Select Trim (' A ' from ' Ahappy ') from dual
--Two. Date function
--1.1. Number of months between two dates
Select Months_between
(To_date (' 02-02-1995 ', ' mm-dd-yyyy '),
To_date (' 01-01-1995 ', ' mm-dd-yyyy ')) from dual
--1.2. Adding a month
Select Add_months (sysdate,1) from dual
--2.1 Date Subtraction
Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual
--2.2 number of months with a two date difference
Select Months_between (sysdate,to_date (' 20111204 ', ' YYYYMMDD ')) from dual
--Three. Conversion functions
--1. Implicitly-function
SELECT * from emp where hiredate= ' 1 July-December-80 '
--2. Show transformations
--2.1to_char () Conversion of dates
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;
--2.2to_char () Conversion of numbers
Select To_char (Sal, ' l9,999.99 ') from EMP
--Four. numeric functions
--1.round (): Rounding
Select Round (12.45,1) from dual
--2.trunc: Truncate
Select Trunc (15.79,1) from dual
--Five. General functions
--NVL and NVL2 Filter empty functions
Select Sal*12 Salary, comm Bonus, SAL*12+NVL (comm,0) from EMP;
Select Sal*12 Salary, comm Bonus, SAL*12+NVL2 (comm,comm,0) from EMP;
--Six. Decode function
Select Ename,decode
(deptno,10, ' engineering Department ',
20, ' personnel department ',
30, ' cleaning Department ') as department
From EMP
where deptno=10
Into ORCL's learning----SQL functions