Oracle Common built-in functions __ static functions

Single-record functions in SQL
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;<?xml:namespace prefix = o ns = "ur N:schemas-microsoft-com:office:office "/>

A A ZERO space
--------- --------- --------- ---------
65 97 48 32

2.CHR
Give an integer and return the corresponding character;
Sql> Select Chr (54740) ZHAO,CHR () chr65 from dual;

En C
-- -
Zhao A

3.CONCAT
Connect two strings;
Sql> Select Concat (' 010-', ' 88888888 ') | | Turn 23 ' Gao Hu bid from dual;

Gao Hu Phone
----------------
010-88888888 ext. 23

4.INITCAP
Returns a string and turns the first letter of the string into uppercase;
Sql> Select Initcap (' Smith ') Upp from dual;

UPP
-----
Smith

5.INSTR (C1,C2,I,J)
Searches for a specified character in a string, returning the location of the specified character;
C1 the string being searched
C2 the string you want to search for
I the start of the search, the default is 1
The position of J appears, default is 1
Sql> Select InStr (' Oracle traning ', ' ra ', 1,2) instring from dual;

Instring
---------
9

6.LENGTH
Returns the length of the string;
Sql> Select Name,length (name), Addr,length (addr), Sal,length (To_char (SAL)) from GAO.NCHAR_TST;

Name Length (name) ADDR Length (ADDR) SAL Length (To_char (SAL))
------ ------------ ---------------- ------------ --------- --------------------
Gao Hu 3 Beijing Sea ingot Area 6 9999.99 7

7.LOWER
Returns a string and converts all characters to lowercase
Sql> Select lower (' AABBCCDD ') AABBCCDD from dual;

Aabbccdd
--------
Aabbccdd

8.UPPER
Returns a string and capitalizes all characters
Sql> Select Upper (' AABBCCDD ') upper from dual;

UPPER
--------
Aabbccdd

9.RPAD and Lpad (paste character)
Rpad paste characters to the right of the column
Lpad paste characters to the left of the 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 deletes the string appearing on the left
RTRIM Delete the string that appears on the right
Sql> Select LTrim (RTrim (' Gao Qian Jing ', '), ') from dual;

LTRIM (RTRIM ('
-------------
Gao Qian Jing

11.SUBSTR (String,start,count)
Take the substring, starting at start, count
Sql> Select substr (' 13088888888 ', 3,8) from dual;

SUBSTR ('
--------
08888888

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

REPLACE (' H
----------
I love You

13.SOUNDEX
Returns a string with the same pronunciation as the given string
Sql> CREATE TABLE table1 (XM varchar (8));
sql> INSERT INTO table1 values (' weather ');
sql> INSERT INTO table1 values (' wether ');
sql> INSERT INTO table1 values (' Gao ');

Sql> Select XM from table1 where Soundex (XM) =soundex (' weather ');

Xm
--------
Weather
Wether

14.TRIM (' s ' from ' string ')
Leading cut off the front characters
Trailing cut off the back characters
If not specified, default is spaces

15.ABS
Returns the absolute values of a specified value
Sql> Select ABS (M), ABS ( -100) from dual;

ABS (-100)
--------- ---------
100 100

16.ACOS
Give the value of the inverse cosine
Sql> Select ACOs ( -1) from dual;

ACOS (-1)
---------
3.1415927

17.ASIN
Give the value of the string anyway
Sql> Select ASIN (0.5) from dual;

ASIN (0.5)
---------
.52359878

18.ATAN
Returns the tangent value of a number
Sql> Select Atan (1) from dual;

Atan (1)
---------
.78539816

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

Ceil (3.1415927)
---------------
4

20.COS
Returns the cosine of a given number
sql> Select cos ( -3.1415927) from dual;

COS (-3.1415927)
---------------
-1

21.COSH
Returns a digital inverse cosine value
Sql> select cosh from dual;

COSH (20)
---------
242582598

22.EXP
Returns the n-th square root of a number E
Sql> Select exp (2), exp (1) from dual;

EXP (2) exp (1)
--------- ---------
7.3890561 2.7182818

23.FLOOR
Take an integer for a given number
Sql> Select Floor (2345.67) from dual;

FLOOR (2345.67)
--------------
2345

24.LN
Returns the numeric value of a number
sql> Select ln (1), ln (2), ln (2.7182818) from dual;

ln (1) ln (2) ln (2.7182818)
--------- --------- -------------
0.69314718.99999999

25.LOG (N1,N2)
Returns the logarithm of a N1-base N2
Sql> Select Log (2,1), log (2,4) from dual;

Log (2,1) log (2,4)
--------- ---------
0 2

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

MoD (10,3) mod (3,3) mod (2,3)
--------- --------- ---------
1 0 2

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

Power (2,10) power (3,3)
----------- ----------
1024 27

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

ROUND (55.5) ROUND ( -55.4) TRUNC (55.5) TRUNC (-55.5)
----------- ------------ ----------- ------------
56-55 55-55

29.SIGN
Take the symbol of number n, greater than 0 to return 1, less than 0 to return-1, equal to 0 to return 0
Sql> Select sign (123), sign ( -100), sign (0) from dual;

SIGN (123) SIGN ( -100) SIGN (0)
--------- ---------- ---------
1-1 0

30.SIN
Returns the sine value of a number
sql> Select sin (1.57079) from dual;

SIN (1.57079)
------------
1

31.SIGH
Returns the value of a hyperbolic sine
sql> Select sin (sinh) from dual;

SIN SINH (20)
--------- ---------
.91294525 242582598

32.SQRT
Returns the root of the number n
Sql> Select sqrt (), sqrt (a) from dual;

SQRT (SQRT) (10)
--------- ---------
8 3.1622777

33.TAN
Returns the tangent value of a number
Sql> Select Tan (m), Tan (a) from dual;

Tan Tan (10)
--------- ---------
2.2371609.64836083

34.TANH
Returns the hyperbolic tangent of a number n
Sql> Select Tanh (), tan from dual;

TANH TAN (20)
--------- ---------
1 2.2371609

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

TRUNC1 TRUNC (124.16666,2)
--------- ------------------
100 124.16

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

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