Oracle + 110 common functions

Oracle + 110 common 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;

A ZERO SPACE

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

65 97 48 32

2. CHR

Returns the corresponding characters;

SQL> select chr (54740) zhao, chr (65) chr65 from dual;

ZH C

---

Zhao

3. CONCAT

Connect two strings;

SQL> select concat (010-, 88888888) | call 23 XXX from dual;

XXX phone number

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

010-88888888 to 23

4. INITCAP

Returns a string and converts the first letter of the string to uppercase;

SQL> select initcap (smith) upp from dual;

UPP

-----

Smith

5. INSTR (C1, C2, I, J)

Searches for a specified character in a string and returns the location where the specified character is found;

String searched by C1

String to be searched by C2

The start position of the I search. The default value is 1.

Where J appears. The default value 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 Haidian District, Beijing 9999.99 7

7. LOWER

Returns a string and lowercase letters of all characters.

SQL> select lower (AaBbCcDd) AaBbCcDd from dual;

AABBCCDD

--------

Aabbccdd

8. UPPER

Returns a string and upper-case all characters.

SQL> select upper (AaBbCcDd) upper from dual;

UPPER

--------

AABBCCDD

9. RPAD and LPAD (paste characters)

Pad the character on the right of the column

LPAD paste characters on the left of the column

SQL> select lpad (rpad (gao, 10, *), 17, *) from dual;

LPAD (RPAD (GAO, 1

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

* ******* Gao *******

If the character is not enough, use * to fill it up.

10. LTRIM and RTRIM

LTRIM deletes the string on the left

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

Substring, starting from start, count

SQL> select substr (13088888888,3, 8) from dual;

SUBSTR (

--------

08888888

12. REPLACE (string, s1, s2)

String: the character or variable to be replaced.

String to be replaced by s1

String to be replaced by s2

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

TRAILING

If this parameter is not specified, the space character is used by default.

15. ABS

Returns the absolute value of a specified value.

SQL> select abs (100), abs (-100) from dual;

ABS (100) ABS (-100)

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

100 100

16. ACOS

Returns the arc cosine value.

SQL> select acos (-1) from dual;

ACOS (-1)

---------

3.1415927

17. ASIN

Returns the arcsin value.

SQL & gt; select asin (0.5) from dual;

ASIN (0.5)

---------

. 52359878

18. ATAN

Returns the arc tangent 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 (-1, 3.1415927)

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

-1

21. COSH

Returns the arc cosine of a number.

SQL> select cosh (20) from dual;

COSH (20)

---------

242582598

22. EXP

Returns the n root of the number e.

SQL> select exp (2), exp (1) from dual;

EXP (2) EXP (1)

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

7.3890561 2.7182818

23. FLOOR

Returns an integer to a given number.

SQL> select floor (2345.67) from dual;

FLOOR (2345.67)

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

2345

24. LN

Returns the logarithm of a number.

SQL> select ln (1), ln (2), ln (2.7182818) from dual;

LN (1) LN (2) LN (1, 2.7182818)

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

0. 69314718. 99999999

25. LOG (n1, n2)

Returns the base n2 logarithm of n1.

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 n1 divided by n2.

SQL> select mod (10, 3), mod (3, 3), mod (2, 3) from dual;

MOD)

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

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

Round 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

Returns 1 if the number n is greater than 0,-1 if the value is less than 0, and 0 if the value is 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 of a number.

SQL> select sin (1.57079) from dual;

SIN (1.57079)

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

1

• 31. SIGH

Returns the hyperbolic sine value.

SQL> select sin (20), sinh (20) from dual;

SIN (20) SINH (20)

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

.. 91294525 242582598

32. SQRT

Returns the root of number n.

SQL> select sqrt (64), sqrt (10) from dual;

SQRT (64) SQRT (10)

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

8 3.1622777

33. TAN

Returns the tangent of a number.

SQL> select tan (20), tan (10) from dual;

TAN (20) TAN (10)

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

2.2371609. 64836083

34. TANH

Returns the hyperbolic tangent of number n.

SQL> select tanh (20), tan (20) from dual;

TANH (20) TAN (20)

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

1 2.2371609

35. TRUNC

Truncate a number based on 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

SQL> select last_day (sysdate) from dual;

LAST_DAY (S

----------

September 31-04

38. MONTHS_BETWEEN (date2, date1)

Given the month of the date2-date1

SQL> select months_between (March---1999) mon_between from dual;

MON_BETWEEN

-----------

9

SQL> selectmonths_between (to_date (2000100005.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)

Returns the date and time in this time zone = other 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)

Returns the date of the week and the date of the next week after week x.

SQL> select next_day ('18-May-2001 ', 'Friday') next_day from dual;

NEXT_DAY

----------

25-5-01