Oracle Single-group functions

--upper-------Convert characters to uppercase

SELECT Upper (' ABCDE ') from dual;

--lower -----Convert characters to lowercase
SELECT Lower (' ABCDE ') from dual;

--initcap----- Returns the first letter of all words in uppercase, other lowercase letters
Select Initcap (ename) from EMP;

--concat----- string connection, equivalent to | |
Select concat (' A ', ' B ') from dual;
Select ' A ' | | ' B ' from dual;

--substr------- Find string
Select substr (' ABCDE ', Length (' ABCDE ')-2) from dual;
Select substr (' ABCDE ', -3,3) from dual;

SUBSTR (String,position,substring_length)

1, if position=0, then be treated as 1

2, if position>1, then from the position position to start looking for

3, if position<1, start from the end of the search

4, if the third position is not specified, from the specified position to the end

--length------ The length of a string
Select Length (dname) from dept;

--replace----- String Substitution
Select replace (ename, ' a ', ' a ') from EMP;

--instr------- String lookup, returns the index value
Select InStr (' Hello world ', ' or ') from dual; --8 indexof

--lpad------ left padding
Select Lpad (' Smith ', ten, ' * ') from dual--to the left padding *****smith

--rpad------ Right padding
Select Rpad (' Smith ', ' Ten, ' * ') from dual--to the right padding smith*****

--trim------- filter The first space
Select Trim (' Mr Smith ') from dual--filter the trailing spaces Mr Smith

--Numerical functions

--round-----Rounding, taking accuracy
Select Round (462,-2) from dual; ------negative number before the decimal point----500
Select Round (412.313,2) from dual; -----positive number----412.13 after the decimal point

--trunc-----Not rounded, directly take maximum value
Select Trunc (462.13,-2) from dual; ----400

--Date function
--months_between ()-----Two hours apart how many months
Select Months_between (sysdate,hiredate) from EMP;

--add_months ()------Add one months to a certain point in time
Select Add_months (sysdate,1) from dual;

--next_day ()
Select Next_day (sysdate, ' Monday ') from dual; ----return to the date of the next Monday

--last_day
Select Last_day (sysdate) from dual; ----Return to the last day of the month

--Conversion function

--to_char
Select To_char (sysdate, ' yyyy ') from dual; ---2011
Select To_char (sysdate, ' Yyyy-mm-dd ') from dual; ----2011-07-16
Select To_char (Sal, ' l999,999,999 ') from EMP; ----
Select To_char (sysdate, ' D ') from dual; --Return to week----7

--to_number
Select To_number (' + ') +to_number (' + ') from dual; ------27

--to_date
Select to_date (' 2009-02-10 ', ' YYYY-MM-DD ') from dual; ----2009/2/10

Select To_char (' 001 ') from dual; ----001
Select To_number (' 003 ') from dual; -----3

--General functions
The--NVL () function-----has a value that returns itself, and no value returns 0
Select NVL (comm,0) from EMP;

--nullif () function----returns null if the expression EXP1 equals the value of EXP2, otherwise returns the value of EXP1

Nullif (EXP1,EXP2)

--nvl2 () function
Select Empno, ename, Sal, Comm, NVL2 (comm, Sal+comm, Sal) total from EMP; ----If Comm is not NULL, returns SAL+COMM, otherwise returns comm

The --coalesce () function --examines each parameter expression in turn, and then stops and returns the value if it encounters a non-null value.

Select Empno, ename, Sal, Comm, COALESCE (Sal+comm, Sal, 0) total revenue from EMP;

--case-expression
Select Empno, ename, Sal,
Case Deptno
When ten then ' finance Department '
When the "Research and Development Department"
When the "sales department"
Else ' Unknown Department '
End Department
from EMP;

--decode () function--similar to the case expression, the decode () function is also used to implement a multi-branch structure

Select Empno, ename, Sal,
Decode (Deptno, 10, ' finance Department ',
20, ' Research and Development Department ',
30, ' Sales department ',
' Unknown department ')
Department
from EMP;

Case has two ways of writing in SQL, first creating a table
CREATE table Salgrade (grade int, Sal int);

INSERT into Salgrade values (1,1000);
INSERT into Salgrade values (2,2000);
INSERT into Salgrade values (3,3000);

The first one is written in a simple way:
Select Grade,sal,
Case Grade
When 1 Then ' low '
When 2 Then ' middle '
Else ' high '
End
From Salgrade;

The second way to find the wording:
SELECT Grade,sal,
case where Sal <=1000 then ' low '
When Sal <=2000 and then ' middle '
Else ' high '
End
From Salgrade;

Decode can only replace the first one:
Select Grade,sal,decode (grade,1, ' low ', 2, ' Middle ', ' high ') from Salgrade;

--Single-line function nesting
Select Empno, Lpad (Initcap (Trim (ename), ten, ") name, job, Sal from EMP;

Oracle Single-group functions