Write in front:
The concept of one-way function:
One-line function: Accept a data, output a data;
Multi-line function: aggregate function, grouping function. Accept a set of data and output a data.
Common character functions
Case-related: LOWER, UPPER, INITCAP
Others: TRIM, LTRIM, RTRIM, LPAD, PPAD, INSTR, SUBSTR, LENGTH, CONCAT, REPLACE
- LOWER converts all characters to lowercase
- UPPER converts all characters to uppercase
- INITCAP converts the initial letter to uppercase
example:
SELECT lower('Hello'),upper('Hello'),initcap('Hello')
FROM dual;- TRIM removes specific characters from the beginning and end of a string
- LTRIM removes specific characters from the beginning of a string
- RTRIM removes specific characters from the end of a string
example:
SELECT trim('H' FROM 'HelloHH'), ltrim('HelloHH', 'H'),rtrim('HelloHH', 'H')
FROM dual;- LPAD Left Completion
- PPAD Completion to the Right
Grammar: lpad (Number, number of bits, fill content) rpad (Number, number of bits, fill content)
example :
SELECT lpad(1000,7,'*'),rpad(1000,7,'*')
FROM dual;- INSTR finds the position of the string (note: In Oracle, the position of the string starts at 1)
Grammar: instr
example :
SELECT instr('hello tom','tom')
FROM dual;- SUBSTR computes strings and cuts a certain length from a certain position to the beginning
Grammar: substr.
example:
SELECT substr('hello',3,2)
FROM dual;- LENGTH Finds the Length of Strings
example :
SELECT length('tom')
FROM dual;- CONCAT connects two strings
example :
SELECT concat('tom','my')
FROM dual;- REPLACE Replacement String
example:
SELECT replace('hello','h','xxx')
FROM dual;Common Character Function Example Integration:
SELECT lower('Hello'),upper('Hello'),initcap('Hello')
FROM dual;
SELECT trim('H' FROM 'HelloHH'), ltrim('HelloHH', 'H'),rtrim('HelloHH', 'H')
FROM dual;
SELECT lpad(1000,7,'*'),rpad(1000,7,'*')
FROM dual;
SELECT instr('hello tom','tom')
FROM dual;
SELECT substr('hello',3,2)
FROM dual;
SELECT length('tom')
FROM dual;
SELECT concat('tom','my')
FROM dual;
SELECT replace('hello','h','xxx')
FROM dual;Common numerical functions
- ROUND rounding
example :
SELECT round(123.456,2),round(123.456,-2),round(123.45,0),round(123.456)
FROM dual;round(123.456,2) decimal points rounded after two decimal points
round(123.456,-2) Rounding the first two decimal points
round(123.45,0) rounded by decimal point
round(123.456) is the same as round(123.456,0).
- TRUNC tail removal
example :
SELECT trunc(12.456,2),trunc(123.456,-2),trunc(123.45,0),trunc(123.456)
FROM dual;trunc(12.456,2) reserved two decimal points
trunc(123.456,-2) Two decimal places are reserved
trunc(123.45,0) is reserved by decimal point
trunc(123.456) is the same as trunc(123.456,0).
- MOD Modeling
example :
SELECT mod(12,5)
FROM dual;Examples of common numerical functions are integrated:
SELECT round(123.456,2),round(123.456,-2),round(123.45,0),round(123.456)
FROM dual;
SELECT trunc(12.456,2),trunc(123.456,-2),trunc(123.45,0),trunc(123.456)
FROM dual;
SELECT mod(12,5)
FROM dual;Common Date Function
- SYSDATE Gets the System Current Date
example :
SELECT sysdate
FROM dual;- MONTHS_BETWEEN Gets the number of months between two dates
example: Get the number of months from the date of entry of employees in the emp table to today
SELECT ename,months_between(sysdate,hiredate)
FROM emp;- ADD_MONTH gets the date in a few months
exmaple: Get the date after three months
SELECT add_months(sysdate,3)
FROM dual;- LAST_DAY Gets the Date of the Last Day of the Month
example :
SELECT last_day(sysdate)
FROM dual;- EXTRACT Gets a Part of the Value from the Date
example: Get the year of the current date
SELECT extract(YEAR FROM sysdate)
FROM dual;example: Month to get the current date
SELECT extract(MONTH FROM sysdate)
FROM dual;example: Get the number of the current date
SELECT extract(DAY FROM sysdate)
FROM dual;Common date function example integration:
SELECT sysdate
FROM dual;
SELECT ename,months_between(sysdate,hiredate)
FROM emp;
SELECT add_months(sysdate,3)
FROM dual;
SELECT last_day(sysdate)
FROM dual;
SELECT extract(YEAR FROM sysdate)
FROM dual;
SELECT extract(MONTH FROM sysdate)
FROM dual;
SELECT extract(DAY FROM sysdate)
FROM dual;Common Conversion Functions
- TO_CHAR Converts a date or value to a character in a specified format
example: Converts a date to a character in a specified format
SELECT to_char(sysdate,'YYYY-MM-DD DAY HH24:MM:SS')
FROM dual;example: Converts a numeric value to a character in a specified format
SELECT to_char(123.45,'L9,999,999.00')
FROM dual;Where L denotes the local character, 00 denotes the decimal point and if there are no two decimal places after the decimal point, it is filled with 0.
- TO_DATE Converts a specified format character to a date
example :
SELECT to_date('1980-01-01','YYYY-MM-DD')
FROM dual;- TO_NUMBER Converts a specified format string to a value
example :
SELECT to_number('$1,222.34','$9,999.99')
FROM dual;Common Conversion Function Example Integration:
SELECT to_char(sysdate,'YYYY-MM-DD DAY HH24:MM:SS')
FROM dual;
SELECT to_char(123.45,'L9,999,999.00')
FROM dual;
SELECT to_date('1980-01-01','YYYY-MM-DD')
FROM dual;
SELECT to_number('$1,222.34','$9,999.99')
FROM dual;Other functions
Null-value conversion function:
- If the NVL field is empty, replace it with the same type
example :
SELECT ename,comm,nvl(comm,0)
FROM emp;If the field comm is empty, replace it with 0 of the same comm value type
- NVL2 returns the second parameter if the field is empty, and vice versa, the third parameter.
example :
SELECT ename,comm,nvl2(comm,0,sal)
FROM emp;If the comm field is empty, replace it with 0, and if not, replace it with sal.
- NULLIF is null if the two fields are the same, otherwise the value of the first parameter is returned.
exmaple :
SELECT nullif('123','123')
FROM dual;Control conversion function example integration:
SELECT ename,comm,nvl(comm,0)
FROM emp;
SELECT ename,comm,nvl2(comm,0,sal)
FROM emp;
SELECT nullif('123','123')
FROM dual;Conditional bifurcation function
- CASE
example :
SELECT ename, deptno, sal,
CASE deptno
WHEN 10 THEN sal + 100
WHEN 20 THEN sal + 200
WHEN 30 THEN sal + 300
ELSE sal END AS salary
FROM emp;- DECODE
example: Increase salary according to department number, department number is 10, salary is 100, department number is 20, salary is 200, department number is 30, salary is 300, others are unchanged.
SELECT ename, deptno, sal,
decode(deptno,
10,sal + 100,
20,sal + 200,
30,sal + 300,
sal) AS salary
FROM emp;SELECT ename, deptno, sal,
CASE
WHEN deptno = 10 THEN sal +100
WHEN deptno = 20 THEN sal +200
WHEN deptno = 30 THEN sal +300
ELSE sal END AS salary
FROM emp;decode is a function and case is an expression
But case has more power.
Examples of conditional bifurcation function integration:
SELECT ename, deptno, sal,
decode(deptno,
10,sal + 100,
20,sal + 200,
30,sal + 300,
sal) AS salary
FROM emp;
SELECT ename, deptno, sal,
CASE deptno
WHEN 10 THEN sal + 100
WHEN 20 THEN sal + 200
WHEN 30 THEN sal + 300
ELSE sal END AS salary
FROM emp;
SELECT ename, deptno, sal,
CASE
WHEN deptno = 10 THEN sal +100
WHEN deptno = 20 THEN sal +200
WHEN deptno = 30 THEN sal +300
ELSE sal END AS salary
FROM emp;