Oracle Functions Encyclopedia _oracle

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 gives an integer and returns the corresponding character;

Sql> Select Chr (54740) ZHAO,CHR () chr65 from dual;

En C
-- -
Zhao A

3.CONCAT connection two strings;

Sql> Select Concat (' 010-', ' 88888888 ') | | Turn 23 ' Gao Hu bid from dual;

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

4.INITCAP returns the 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 a string for the specified character, 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. 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 the string and capitalizes all the 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 ', 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 (' heloveyou ', ' he ', ' I ')
------------------------------
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 value of the specified values

Sql> Select ABS (M), ABS ( -100) from dual;

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

16.ACOS gives the value of the inverse cosine

Sql> Select ACOs ( -1) from dual;

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

17.ASIN gives the value of the inverse chord

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 number given

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 takes 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 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 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 square root of 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 takes the number n symbol, is greater than 0 returns 1, is less than 0 returns-1, equals 0 returns 0

Sql> Select sign (123), sign ( -100), sign (0) from dual;

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

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

1-1 0

30.SIN returns the sinusoidal value of a number

sql> Select sin (1.57079) from dual;

SIN (1.57079)

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

1

31.SIGH returns the value of the 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 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 ', ' 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

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

41.SYSDATE to get the current date of the system

Sql> Select To_char (sysdate, ' dd-mm-yyyy Day ') from dual;

To_char (Sysdate, '

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

09-05-2004 Sunday

Trunc (DATE,FMT) truncates the date according to the requirement given, if fmt= ' mi ' indicates a reservation, truncated seconds

Sql> Select To_char (trunc (sysdate, ' hh '), ' yyyy.mm.dd hh24:mi:ss ') hh,

2 To_char (trunc (sysdate, ' mi '), ' yyyy.mm.dd hh24:mi:ss ') hhmm from dual;

HH HHMM

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

2004.05.09 11:00:00 2004.05.09 11:17:00

42.CHARTOROWID convert character data type to ROWID type

Sql> Select Rowid,rowidtochar (ROWID), ename from Scott.emp;

ROWID Rowidtochar (ROWID) ename

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

AAAAFKAACAAAAEQAAA aaaafkaacaaaaeqaaa SMITH

Aaaafkaacaaaaeqaab Aaaafkaacaaaaeqaab ALLEN

AAAAFKAACAAAAEQAAC AAAAFKAACAAAAEQAAC WARD

Aaaafkaacaaaaeqaad Aaaafkaacaaaaeqaad JONES