Oracle single row Functions

1. Although each database supports SQL statements, each database also has operation functions supported by each database. These are single-row functions. If you want to develop a database, in addition to SQL statements, you must learn more functions.
1. single-row function classification:
Character functions, numeric functions, date functions, conversion functions, and general functions.
2. Character functions: this function is mainly used for string operations.
Upper (string | column): converts the input string to uppercase and returns the result.
Lower (string | column): converts the input string to lowercase and returns the result.
Initcap (string | column): starts with an uppercase letter.
Length (string | column): returns the length of the string.
Replace (string | column): replace
Substr (string | column, start point [end point]): String Truncation
Oracle is a little troublesome. Even if you want to verify the string, you must write a complete SQL statement. Therefore, for the convenience of your query in oracle, therefore, a virtual table "dual" is provided:
1): Observe the function for converting to uppercase
Select upper ('hello') from dual
Usage of capital conversion: in general use, do users care whether the database stored in upper or lower case by using the "upper case" or "lower case" method when entering data?
Select * from emp where ename = '& str'
If the input is in lower case, the data cannot be found.
Answer: The user obviously does not care, so the program can only process the data entered by the user. (Add a conversion function)

Select * from emp where ename = upper ('& str ');
SQL> select * from emp where ename = upper ('& str'); input str value: smith original value 1: select * from emp where ename = upper ('& str ') new value 1: select * from emp where ename = upper ('Smith ')
Empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- -------------- ---------- 7369 smith clerk 7902 17-12 month-80 800 20
Note: The above '&' is the content of the substitution variable.
2) convert lowercase letters:
Example 1: Return select lower (ename) from emp in lowercase for all employee names
Example 2: capital the first letter of each employee's name.
Select initcap (ename) from emp
3) length Functions
Example 3: query the length of each employee's name
Select ename, length (ename) from emp
Example 4: query the employee information whose name is 5.
Select ename, length (ename) from emp where length (ename) = 5
4) replacement function
Example 5: Use '_' to replace all the letters 'A' in the name ';
Select replace (ename, 'A', '_') from emp

5) string truncation:
A: substr (string | column, start point): truncates from the start point to the end.
Select ename, substr (ename, 3) from emp
B: substr (string | column, start point, end point): capture from start point to end point:
Select ename, substr (ename,) from emp select ename, substr (ename,) from emp
Example 1: extract the last three characters of each employee's name:
Select ename, substr (ename, length (ename)-2) from emp
SetNegative number: Indicates truncation from the back to the front:Select ename, substr (ename,-3) from emp
Interview question: does the following table intercepted by the substr () function start from 0 or 1?
Answer: (1) in oracle databases, the values start from 0 to 1.
(2): SUBSTR can also be set to a negative number, indicating the truncation point starting from the specified end.

3. Numeric functions:
There are three numeric functions:
ROUND (number | column, [number of digits to retain decimal places]): rounding operation.

TRUNC (number | column, [number of digits retained]): discard the content at the specified position.
MOD (number 1, number 2): modulo operation (remainder)

Example 1: Verify the round () function:
Select round (903.5) from dual
SQL> select role (903.5) from dual;
ROUND (903.5) ------------ 904

SQL> select round (903.53567), round (-903.53567) from dual;
ROUND (903.53567) ROUND (-903.53567) ---------------- ----------------- 904-904

SQL> select round (903.53567), round (903.53567, 2) from dual;
ROUND (903.53567) ROUND (903.53567, 2) ---------------- ------------------ 904 903.54

SQL> select round (903.53567), round (903.53567,-1) from dual;
ROUND (903.53567) ROUND (903.53567,-1) ---------------- --------------------- 904 900

SQL> select round (903.53567), round (-903.53567), round (903.53567,-1), round (903.53567, 2) from dual;
ROUND (903.53567) ROUND (-903.53567) ROUND (903.53567,-1) ROUND (903.53567, 2) ---------------- ----------------- -------------------------- 904-904 900

Example 2: Verify the TRUNC () function:

SQL> select trunc (903.53567), trunc (-903.53567), trunc (903.53567,-1), trunc (903.53567, 2) from dual;
TRUNC (903.53567) TRUNC (-903.53567) TRUNC (903.53567,-1) TRUNC (903.53567, 2) ---------------- ----------------- ---------------------------- 903-903 900

Example 3. Obtain the remainder function:
Select mod (10, 3) from dual
4. Date functions:
1) Get the current date: Get it with sysdate
SQL> select sysdate from dual;
SYSDATE -------------- 28-6-13
Note: operations can also be performed on dates:
Date + number = Date: indicates the date after several days
SQL> select sysdate + 3, sysdate + 300 from dual;
SYSDATE + 3 SYSDATE + 300 -------------- 01-7 months-13 24-4 months-14

Date-number = Date: indicates the date before several days

SQL> select sysdate-3, sysdate-300 from dual;
SYSDATE-3 SYSDATE-300 -------------- 25-6 month-13 01-9 month-12
Date-date = Number: indicates the number of days between days: but it must be a big date-a small date.
----> Find the number of employment days for each employee until today:
SQL> select ename, hiredate, sysdate-hiredate from emp;
Ename hiredate SYSDATE-HIREDATE ---------- ------------ ---------------- SMITH 17-12-80 11881.4943 ALLEN 20-2 month-81 11816.4943 WARD 22-2 month-81 11814.4943 JONES 02-month-81 11775.4943 MARTIN 28-9 month-81 11596.4943 blke 01-5 month- 81 11746.4943 CLARK 09-6 month-81 11707.4943 SCOTT 19-4 month-87 9567.49432 KING 17-11 month-81 11546.4943 TURNER 08-9 month-81 11616.4943 ADAMS 23-5 month-87 9533.49432
Ename hiredate SYSDATE-HIREDATE ---------- -------------- ---------------- JAMES 03-12 month-81 11530.4943 FORD 03-12 month-81 11530.4943 MILLER 23-1 month-82 11479.4943
2) Four other date-related operation functions:
LAST_DAY: returns the last day of the specified date.
---> Obtain the date of the last day of the month:

SQL> select last_day (sysdate) from dual;
LAST_DAY (SYSDA ------------ 30-6 months-13
NEXT_DAY (date, number of weeks): Calculate the date of the next specified week x.
SQL> select next_day (sysdate, 'monday') from dual;
NEXT_DAY (SYSDA -------------- 01-7-13

ADD_MONTHS (date, number): returns the date after several months.

SQL> select add_months (sysdate, 4) from dual;
ADD_MONTHS (SYS -------------- 28-10-13
MONTHS_BETWEEN (date 1, date 2): returns the month that has elapsed between two dates.
--> Find the month of employment for each employee until today:
SQL> select ename, hiredate, trunc (months_between (sysdate, hiredate) from emp;
Ename hiredate trunc (MONTHS_BETWEEN (SYSDATE, HIREDATE )) ---------- -------------- SMITH 17-12 months-80 390 ALLEN 20-2 month-81 388 WARD 22-2 month-81 388 JONES 02-4 month-81 386 MARTIN 28-9 month-81 381 BLAKE 01-5 month-81 385 CLARK -81 384 SCOTT 19-4 month-87 314 KING 17-11 month-81 379 TURNER 08-9 month-81 381 ADAMS 23-5 month-87 313
Ename hiredate trunc (MONTHS_BETWEEN (SYSDATE, HIREDATE) ---------- ------------ monthly JAMES 03-12 month-81 378 FORD 03-12 month-81 378 MILLER 23-1 month-82 377

5. conversion functions:
Three types of data in the oracle database are available: number, string (varchar2), and date ). the main function of the conversion function is to convert these data types.
TO_CHAR (string | column, Format String): converts a date or number to string data display.
--> Format the date as a string:

SQL> select to_char (sysdate, 'yyyy-mm-dd') from dual;
TO_CHAR (SY ----------

2013-06-28

SQL> select to_char (sysdate, 'yyyy-mm-dd'), 2 to_char (sysdate, 'yyyy'), 3 to_char (sysdate, 'mm '), 4 to_char (sysdate, 'dd') from dual;
TO_CHAR (SY TO_C TO ---------- ---- -- 2013-06-28 2013 06 28
In this case, the data displayed will display the leading 0. To remove the leading 0, add a 'ffm'
SQL> select to_char (sysdate, 'fmyyyy-mm-dd') from dual;
TO_CHAR (SY ---------- 2013-6-28

Note: in oracle, date contains the time. To display the time, write as follows:
SQL> select to_char (sysdate, 'fmyyyy-mm-dd hh: mi: ss') from dual;
TO_CHAR (SYSDATE, 'fm ------------------- 12:17:30

SQL> select to_char (sysdate, 'fmyyyy-mm-dd hh24: mi: ss') from dual;
TO_CHAR (SYSDATE, 'fm ------------------- 12:18:32

TO_DAET (string, Format String): Convert string to DATE data display.
SQL> select to_date ('2017-03-09 ', 'yyyy-mm-dd') from dual;
TO_DATE ('2017------------- 09-3-89

TO_NUMBER (string): converts a string to a number for display. (Not required)
SQL> select to_number ('1') from dual;
TO_NUMBER ('1') ------------ 1

SQL> select to_number ('1') + to_number ('2') from dual;
TO_NUMBER ('1') + TO_NUMBER ('2') ----------------------------- 3

6. Common functions:
There are two common functions: nvl () function, decode ().
(1) nvl () function: Processing Null
Query the total annual salary of each employee:

SQL> select ename, sal, comm, (sal + comm) * 12 from emp;
Ename sal comm (SAL + COMM) * 12 ------------------------------------------- The SMITH 800 ALLEN 1600 300 22800 WARD 1250 500 21000 JONES 2975 MARTIN 1250 1400 31800 BLAKE 2850 CLARK 2450 SCOTT 1, 3000 KING 5000 TURNER 1500 0 18000 ADAMS 1100
Ename sal comm (SAL + COMM) * 12 ------------------------------------------- JAMES 950 FORD 3000 MILLER 1300

Note: When an employee's annual salary changes to null, the key to this problem is that the employee's comm field is null. To solve this problem, you must make a process to change the comm field to 0, and the nvl function can be implemented.
SQL> select ename, sal, comm, (sal + comm) * 12, nvl (comm, 0) from emp;
Ename sal comm (SAL + COMM) * 12 NVL (COMM, 0) ---------- ------------- ----------- SMITH 800 0 ALLEN 1600 300 22800 300 WARD 1250 500 21000 500 2975 JONES 1250 0 MARTIN 1400 31800 1400 BLAKE 2850 0 CLARK 2450 0 SCOTT 3000 0 KING 5000 0 TURNER 1500 0 18000 0 ADAMS 1100 0
Ename sal comm (SAL + COMM) * 12 NVL (COMM, 0) ---------- ------------- ----------- JAMES 950 0 FORD 3000 0 MILLER 1300 0

SQL> select ename, sal, comm, (sal + nvl (comm, 0) * 12 from emp;
Ename sal comm (SAL + NVL (COMM, 0 )) * 12 ---------- ------------------ SMITH 800 9600 ALLEN 1600 300 22800 WARD 1250 500 21000 JONES 2975 35700 MARTIN 1250 1400 31800 BLAKE 2850 CLARK 34200 2450 SCOTT 29400 3000 KING 36000 TURNER 5000 0 60000 ADAMS 1100 13200
Ename sal comm (SAL + NVL (COMM, 0) * 12 ---------- -------------------- JAMES 950 11400 FORD 3000 36000 MILLER 1300 15600

(2) decode () function: multi-value judgment:
The decode function is very similar to the if/else statement in the program. The only difference is that the decode function determines a value rather than a logical condition.
For example, you need to display the positions of all employees, but these positions must be displayed in Chinese.
CLERK: clerk salesman: salesman manager: manager analyst: analyst president: PRESIDENT
This kind of judgment must be a row-by-row judgment, so decode () must be used at this time.
Decode function Syntax: decode (value | column, judgment value 1, display value 1, judgment value 2, display value 2 ....)

Select empno, ename, job, decode (job, 'cler', 'clerks ', 'salesman', 'salesman', 'manager', 'manager', 'analyst ', 'Analyst ', 'President', 'President') as job from emp

Empno ename job --------- -------- --------- ------ 7369 smith clerk 7499 allen salesman 7521 ward salesman 7566 jones manager 7654 martin salesman 7698 blke MANAGER 7782 clark manager 7788 SCOTT ANALYST 7839 king president 7844 turner salesman 7876 ADAMS CLERK
Empno ename job --------- -------- --------- ------ 7900 james clerk 7902 ford analyst 7934 MILLER CLERK