Oracle function

One-line function:

One-line functions are mainly divided into the following five categories: character functions, numeric functions, date functions, conversion functions, and general functions.

Character function

UPPER (string | column): returns the input string in uppercase

LOWER (string | column): returns the input string in lowercase

INITCAP (string | column): initials are capitalized

LENGTH (string | column): find the length of the string

REPLACE (string | column): replace

SUBSTR (string | column, start point [, end point]): string interception

Turn to uppercase functions:

SELECT UPPER ('hello') FROM dual

Operation of turning to lowercase:

SELECT LOWER (ename) FROM emp

Capitalize the initials of each employee's name:

SELECT INITCAP (ename) FROM emp

Find out the length of each employee's name:

SELECT ename,LENGTH (ename) FROM emp

Replace all the letters "A" in the name with the letter "_":

SELECT REPLACE (ename,'A','_') FROM emp

There are two syntax for string interception

Syntax 1: SUBSTR (string | column, starting point), which means that it is truncated from the starting point to the end

SELECT ename,SUBSTR (ename,3) FROM emp

SELECT ename,SUBSTR (ename,0,3) FROM emp

SELECT ename,SUBSTR (ename,1,3) FROM emp

Numeric function

ROUND (number | column [, number of decimal places retained]): the operation of rounding

TRUNC (number | column [, number of decimal places retained]): discard the contents of the specified location

MOD (number 1, number 2): take the module, take the remainder

SELECT ROUND (903.53567), ROUND (- 903.53567), ROUND (903.53567), ROUND (903.53567) FROM dual

SELECT MOD (10jue 3) FROM dual

Date function

Current date:

SELECT SYSDATE FROM dual

Several calculations can also be made in the date:

Date + number = date, indicating the date after several days:

SELECT SYSDATE + 3SYSDATE + 300FROM dual

Date-number = date, indicating the date several days ago:

SELECT SYSDATE-3 Syndate-300 FROM dual

Date-date = number, which indicates the number of days between two days, but it must be a big date-a small date

LAST_DAY (date): find the last day of the specified date:

SELECT LAST_DAY (SYSDATE) FROM dual

NEXT_DAY (date, day of the week): find the date of the next specified week X:

SELECT NEXT_DAY (SYSDATE,' Monday') FROM dual

ADD_MONTHS (date, number): find the date after a number of months:

SELECT ADD_MONTHS (SYSDATE,4) FROM dual

MONTHS_BETWEEN (date 1, date 2): find the month experienced between two dates:

SELECT ename,hiredate,TRUNC (MONTHS_BETWEEN (SYSDATE,hiredate)) FROM emp

Conversion function

TO_CHAR (string | column, format string): change a date or number into a string to display

TO_DATE (string, format string): turn a string into a DATE data display

TO_NUMBER (string): turn a string into a numeric display

SELECT TO_CHAR (SYSDATE,'yyyy-mm-dd'), TO_CHAR (SYSDATE,'yyyy') year,TO_CHAR (SYSDATE,'mm') months,TO_CHAR (SYSDATE,'dd') day FROM dual

SELECT TO_DATE (1989-09-12) FROM dual

SELECT TO_NUMBER ('1') + TO_NUMBER ('2') FROM dual

General function

The NVL () function, which handles null:

SELECT ename,sal,comm, (sal+NVL (comm,0)) * 12meme NVL (comm,0) FROM emp

DECODE () function: multi-value judgment

SELECT empno,ename,job,DECODE (job,'CLERK',' Clerk, SALESMAN',' salesperson, MANAGER',' Manager, ANALYST',' analyst, PRESIDENT',' President) FROM emp

Statistical function

COUNT (): query the data records in the table

AVG (): find the average

SUM (): summation

MAX (): find the maximum

MIN (): find the minimum

SELECT COUNT (empno), SUM (sal), AVG (sal) FROM emp

SELECT MAX (sal), MIN (sal) FROM emp

SELECT COUNT (ename) FROM BONUS