Oracle built-in functions

1.ASCII returns the decimal number corresponding to the specified character;
Sql>select ASCII (' a ') a,ascii (' A ') a,ascii (' 0 ') zero,ascii (') space from dual;

2.CHR gives an integer that returns the corresponding character;

Sql>select chr (55203) ZHU,CHR (+) chr65 from dual;

3.concat connection two strings;

Sql>select concat (' 010-', ' 88888888 ') | | ' Ext. 23 ' A company phone from dual;

4.INITCAP returns a string and capitalizes the first letter of the string;

Sql>select initcap (' Smith ') Upp from dual;

5.INSTR (c1,c2,m,n) searches for a specified character in a string, returning the position of the found character;

C1: the string being searched for

C2: string to search for

M: Start position of search, default is 1

N: The location that appears, default to 1

Sql>select InStr (' Oracle traning ', ' ra ', ' n ') instring from dual;

6.LENGTH returns the length of the string;

7.LOWER returns a string, and all characters are lowercase

Sql> Select lower (' AABBCCDD ') AABBCCDD from dual;

8.UPPER returns a string and capitalizes all characters

Sql> Select Upper (' AABBCCDD ') upper from dual;

9.RPAD and Lpad (pasting characters)
Rpad pasting characters to the right of a column
Lpad pasting characters to the left of a column
Sql> Select Lpad (Rpad (' Gao ', ' ten, ' * '), +, ' * ') from dual;

Lpad (Rpad (' GAO ', 1
-----------------
gao*******
Not enough characters to fill with *

10.LTRIM and RTrim
LTRIM Delete the string that appears to the left
RTRIM Delete the string that appears to the right
Sql> Select LTrim (RTrim (' Gao Qian Jing ', '), ') from dual;

11.SUBSTR (String,start,count)
Takes a substring, starting with start, taking count
Sql> Select substr (' 13088888888 ', 3,8) from dual;

12.REPLACE (' string ', ' s1 ', ' s2 ')
The character or variable that string wants to be replaced
S1 replaced string
S2 the string to replace
sql> Select replace (' He love you ', ' he ', ' I ') from dual;

13.TRIM (' s ' from ' string ')
Leading cut off the front character
TRAILING cut off the back of the character
If not specified, the default is whitespace

14.ABS returns the absolute value of the specified values
Sql> Select ABS (+), ABS ( -100) from dual;

15.CEIL returns the smallest integer greater than or equal to the given number
Sql> Select Ceil (3.1415927) from dual;

16.FLOOR takes an integer to a given number
Sql> Select Floor (2345.67) from dual;

1726.MOD (N1,N2) returns the remainder of a N1 divided by N2
Sql> Select mod (10,3), mod (3,3), mod (2,3) from dual;

18.POWER returns the N2 root of N1
Sql> Select Power (2,10), Power (3,3) from dual;

19.ROUND and Trunc
Rounding with the specified precision
Sql> Select Round (55.5), round ( -55.4), trunc (55.5), Trunc ( -55.5) from dual;

20.SIGN take the number n symbol, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0
Sql> Select sign (123), sign ( -100), sign (0) from dual;

21.TRUNC
Intercepts a number according to the specified precision
Sql> Select Trunc (124.1666,-2) Trunc1,trunc (124.16666,2) from dual;

22.add_months
Add or subtract a month
Sql> Select To_char (add_months (to_date (' 199912 ', ' yyyymm '), 2), ' Yyyymm ') from dual;

Sql> Select To_char (add_months (to_date (' 199912 ', ' yyyymm '), -2), ' Yyyymm ') from dual;

23.last_day
Returns the last day of the date
Sql> Select To_char (sysdate, ' yyyy.mm.dd '), To_char ((sysdate) +1, ' Yyyy.mm.dd ') from dual;

24.months_between (DATE2,DATE1)
Give the month of date2-date1
Sql> Select Months_between (' 1 September-December-1999 ', ' 1 September-March -1999 ') Mon_between from dual;

Mon_between
-----------
9
Sql>selectmonths_between (to_date (' 2000.05.20 ', ' yyyy.mm.dd '), to_date (' 2005.05.20 ', ' yyyy.dd ')) MON_BETW from Dual

Mon_betw
---------
-60

25.new_time (date, ' This ', ' that ')
Give the date and time of the =other time zone in this time zone
Sql> Select To_char (sysdate, ' yyyy.mm.dd hh24:mi:ss ') Bj_time,to_char (new_time
2 (sysdate, ' PDT ', ' GMT '), ' yyyy.mm.dd hh24:mi:ss ') los_angles from dual;

Bj_time Los_angles
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32

26.SYSDATE to get the current date of the system
Sql> Select To_char (sysdate, "Dd-mm-yyyy Day") from dual;

27.to_date (String, ' format ') converts a string to a date in Oracle

28.to_number
Converts the given character to a number

29.GROUP by is used primarily to count a set of numbers
Sql> Select Deptno,count (*), sum (SAL) from the Scott.emp group by Deptno;

DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400

30..HAVING to group statistics plus limit conditions
Sql> Select Deptno,count (*), sum (SAL) from Scott.emp Group by DEPTNO have Counnt (*) >=5;

DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20 5 10875
30 6 9400
Sql> Select Deptno,count (*), sum (SAL) from Scott.emp have count (*) >=5 GROUP by TNO;

DEPTNO COUNT (*) SUM (SAL)
--------- --------- ---------
20 5 10875
30 6 9400

31.ORDER by is used to sort output the results of a query
Sql> Select Deptno,ename,sal from scott.emp order by deptno,sal Desc;

DEPTNO ename SAL
--------- ---------- ---------
Ten KING 5000
CLARK 2450
Ten MILLER 1300
SCOTT 3000
FORD 3000
JONES 2975
ADAMS 1100
800 SMITH
2850 BLAKE
ALLEN 1600
1500 TURNER
WARD 1250
MARTIN 1250
JAMES 950