oracle+110 a common function

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;

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) | | Transfer xxx telephone from dual;

XXX Telephone

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

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))

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

XXX 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 characters)

Rpad paste characters to the right of the column

Lpad paste characters to the left of the column

Sql> Select Lpad (Rpad (gao,10,*), 17,*) 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;

To_cha

------

199910

37.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;

To_char (SY to_char (S

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

2004.05.09 2004.05.10

Sql> Select Last_day (sysdate) from dual;

Last_day (S

----------

3 January-May-04

38.months_between (DATE2,DATE1)

Give out the month of date2-date1

Sql> Select Months_between (1 September-December -1999,19-3 month -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.MM.DD)) mon_betw from dual;

Mon_betw

---------

-60

39.new_time (Date,this,that)

Gives 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

40.next_day (Date,day)

Give date date and week x to calculate the next one weeks

Sql> Select Next_day (' 1 August-May-2001 ', ' Friday ') next_day from dual;

Next_day

----------

2 May-May-01