Oracle SQL functions

Oracle broadly divides functions into single-line functions, aggregate functions, and analytic functions.

Single-line functions are divided into character functions, date functions, conversion functions, numeric functions, general functions, decode functions

I. Character functions

1) Casing Control function

01.Lower () All lowercase

--character function--lowercase select Lower (' HAPPY ') from dual;

Effect:

02.Upper () All Caps

--Uppercase Select Upper (' Happy ') from dual;

Effect:

03.initcap () First letter capitalization

--Capitalize the first letter select Initcap (' Happy ') from dual;

Effect:

2) Character control function

01.concat () stitching

--Splicing Select concat (' Happy ', ' boy ') from dual;

Effect:

02.SUBSTR () Intercept characters

--intercept string position from 1 onwards select substr (' Happyboy ', 6) from dual;

Effect:

--to intercept the character, starting position, take a few characters select substr (' Happyboy ', 6,3) from dual;

Effect:

03.length () and LENGTHB ()

  --length (' string '): Number of characters statistics   --LENGTHB (' string '): bytes count Select length (' hehe ') characters, LENGTHB (' hehe ') as bytes from dual;

Effect:

04.instr ()

--instr (' Large string ', ' small string ') returns the position of a small string in a large string, select InStr (' Happy hehe ', ' he ', 2,2) ' instring ' from dual;

Select InStr (' Happy hehe ', ' he ', -2,2) ' reversed instring ' from dual;

Effect:

Select InStr (' Happy hehe ', ' he ', 2,2) ' instring in bytes ' from dual;

Effect:

05.lpad () and Rpad ()

--lpad () and Rpad () Select Lpad (' Happy ', ten, ' * ') from dual;

Effect:

Two. Date function

1) Date function

01. Number of months between two dates

Select Months_between (to_date (' 02-02-1995 ', ' mm-dd-yyyy '), to_date (' 01-01-1995 ', ' mm-dd-yyyy ') "MONTHS" from dual;

Effect:

02. Add a number of months to the specified date

--Add a number of months to the specified date select To_char (Add_months (hiredate,1), ' dd-mon-yyyy ') "Next month" from Empwhere ename= ' JONES ';

Effect:

2) Date Subtraction

01. Number of days between two dates

--Number of days between two dates select floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;

Effect:

02. Number of months between two dates

--Two months of the month difference between select Months_between (sysdate,to_date (' 20111204 ', ' YYYYMMDD ')) from dual;

Effect:

Three. Conversion functions

1) Implicit conversion

--conversion function--implicit function select * from Empwhere hiredate= ' 1 July-December-80 ';

Effect:

2) Display Conversion

01.to_char () Conversion of dates

--Explicit function   --01.to_char () conversion of a date   select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Effect:

02.to_char () Conversion of numbers

--02.to_char () The conversion of a number   select To_char (Sal, ' l9,999.99 ') from    EMP;

Effect:

Four. Numeric functions

01.Round ()

--Number function  --01.round () rounding  Select Round (12.45,1) from dual;

Effect:

02.trunc () truncation

--02.trunc () Truncate  select Trunc (15.19,1) "Truncate" from dual;

Effect:

Five. General functions

NVL and NVL2 Filter empty functions

01.NVL Filter Empty function

Select Sal*12 Salary, comm Bonus, SAL*12+NVL (comm,0) from EMP;

Effect:

02.NVL2 Filter Empty function

Select Sal*12 Salary, comm Bonus, SAL*12+NVL2 (comm,comm,0) from EMP;

Effect:

Six. Decode function

--decode function  Select Ename,empno,      decode (ename, ' SMITH ', 1,      ' ALLEN ', 2,      ' WARD ', 3,      ' JONES ', 4) ' Location ' from      EMP '      where empno<7600      order by empno, ' location '

Effect:

Oracle SQL functions