Oracle's functions

Oracle's function classification

Single-line function: Calculates the input values for each row, obtains the corresponding calculation result, returns to the user, namely,

Each row acts as an input parameter, and the calculation results of each row are calculated by the function. For example, length

Multi-line function: The multi-line input value is calculated to obtain multiple rows of the corresponding results, such as Max,min

Single-line function

Character functions:

Business for handling characters

Replace (char 1,search_string,replace_string)

Example:

in the EMP ename in the table column to replace all a

Select Replace (ename, ' a ', ' replaced by a ') from EMP;

InStr (C1,C2,I,J)

C1 string to be searched

C2 the string you want to search

I where the search started

Where J appears, default is 1

Example:

Select InStr (' Oracle index string function ', ' s ', up to) from dual;

ASCII (' characters to convert ') returns the decimal number corresponding to the specified character

Case:

Select ASCII (' a ') a,ascii (' a ') as A,ascii (' 0 ') as Zero,ascii (") as space from dual;

chr (int) give an integer, return the corresponding character

case:

Select Chr (54740) as ZH,CHR (all) as C6 from dual;

concat Connection String

Case:

Select Concat (ename, ' is a good person ') from EMP;

equivalent:

Select Ename | | ' is a good person ' from EMP;

Initcap character Conversion first letter uppercase

Case:

Select Initcap (' abc ') from dual;

length calculates the string length, no distinction between Chinese and English

case:

Find out if the characters in the EMP table are 4 employees

SELECT * from emp where length (ename) = 4;

Lower and Upper function change all lowercase/ Capitalize all

Comprehensive:

Put the first letter of the employee's initials in lowercase, other letters capitalized?

Select Concat (Lower (substr (ename,1,1)), Upper (substr (Ename,2,length (ename)-1))) from EMP;

Lpad and Rpad left padding/ Right padding

Case:

Select Lpad (' test ', +, ' left fill ') from dual;

LTrim and RTrim sit cut/ Crop Right

Case:

Select RTrim (' Right trim oooooooo ', ' O ') from dual;

-------------------------------------------------------

Crop All O

Trim crop the specified character, only support single-character

Select Trim (' t ' from ' the was a trim ') from dual;

Numeric functions:

ceil function for rounding up

Select Ceil (3.1415926) from dual;

floor for rounding down

mod (m,n) Modulo , if n is 0 , return M .

round function, for rounding

Trunk function, used to intercept an integer

add_months ( date value, increase | | month of reduction)

Show employees who have been in employment for the last three months

-------------------------------------------

sysdate: Show Current Date

Last_day Show last day of the month

Next_day displays the most recently specified date

to_char (number) convert a specified numeric format

Case:

Select To_char (Sal, ' l999g999d99 ') from EMP;

9: represents a number

L : Represents the local currency style

G: grouping separators (using localization)

D: represents a comma

according to Deptno The number shows different information

Select Decode (deptno,10, ' 10th Department ', 20, ' 20th Department ', 30, ' 30th Department ') from EMP;

to_date (String,format); -- convert string to date

Example:

INSERT into EMP (empno,hiredate) VALUES (7777,to_date (' 2017-8-6 ', ' yyyy-mm-dd '));

Description

To_char is to convert the date into characters

to_date is to convert the character to a date

System functions:

Function: Used for querying system information;

Video notes:

Tips for using subqueries to complete row migrations

format: create TABLE table name as select column name 1[, column Name 2 ...] From to copy table [where]

To complete an update using a subquery

case:

Hope employees Scott of jobs, wages, subsidies and Smith Employees alike

Tradition: Update emp Set job = (select Job from emp where ename = ' Smith '), Sal = (Sele ...), comm = (Sele ...) where enmae = ' Scott ' ;

Quick: update emp Set (JOB,SAL,COMM) = (select Job,sal,comm from emp where ename = ' Smith) where ename = ' Scott ';

Oracle's functions