Functions commonly used by 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 that returns the corresponding character;

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

ZH C
-- -
Zhao A

3.CONCAT connection two strings;

Sql> Select Concat (' 010-', ' 88888888 ') | | ' Ext. 23 ' Gao Hu call from dual;

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

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

Sql> Select Initcap (' Smith ') Upp from dual;

UPP
-----
Smith

5.INSTR (c1,c2,i,j) searches a string for the specified character and returns the position of the specified character;

C1 string to be searched
C2 the string you want to search
I Search start position, default is 1
Where J appears, default is 1
Sql> Select InStr (' Oracle traning ', ' ra ', ' n ') 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 all characters are 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 (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;

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

11.SUBSTR (String,start,count)

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

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

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;

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 character
TRAILING cut off the back of the character
If not specified, the default is whitespace

15.ABS returns the absolute value of the specified values

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

ABS (+) 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 inverse 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 numeric inverse cosine value

Sql> select cosh from dual;

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

22.EXP returns the n-th 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 to a given number

Sql> Select Floor (2345.67) from dual;

Floor (2345.67)
--------------
2345

24.LN returns the pair 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 a logarithm with N1 as the 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 the remainder of a N1 divided by 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 N1

Sql> Select Power (2,10), Power (3,3) from dual;

Power (2,10) power (3,3)

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

1024 27

28.ROUND and Trunc

Rounding with 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 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;

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 (ten) from dual;

SQRT (SQRT) (10)

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

8 3.1622777

33.TAN returns the tangent value of a number

Sql> Select Tan, Tan (ten) from dual;

Tan (10)

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

2.2371609.64836083

34.TANH

Returns the hyperbolic tangent value of the number n

Sql> Select Tanh, Tan (a) 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 a 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

Returns the last day of the current month.

Sql> Select Last_day (sysdate) from dual;

Last_day (S

----------

3 January-May-04

38.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

39.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

40.next_day (date, ' Day ')

Date given date and week x after 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 given requirements if the fmt= ' mi ' represents a reserved point, truncating the 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 converting character data types to ROWID types

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

Functions commonly used by Oracle