Oracle SQL built-in functions

Source: Internet
Author: User
Tags acos asin date1

Single-record functions in SQL

1. ASCIIReturns 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. CHRReturns the corresponding characters;
SQL> select CHR (54740) Zhao, CHR (65) chr65 from dual;

Zh C
---
Zhao

3. ConcatConnect two strings;
SQL> select Concat ('010-', '000000') |' to 23' Gao Qian competing phone number from dual;

Gao Qian's phone number
----------------
010-88888888 to 23

4. initcapReturns 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', 'A', 1, 2) instring from dual;

Instring
---------
9

6. LengthReturns 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 Qian Jing 3 Beijing haiding District 6 9999.99 7

7. LowerReturns a string and lowercase letters of all characters.
SQL> select lower ('abbccdd') aabbccdd from dual;

Aabbccdd
--------
Aabbccdd

8. UpperReturns a string and upper-case all characters.
SQL> select upper ('abbccdd') 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 ('123', 13088888888) 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', 'hes', 'I') from dual;

Replace ('heloveyou', 'hes', 'I ')
------------------------------
I love you

13. soundexReturns a string with the same pronunciation as a given string.
SQL> Create Table Table1 (XM varchar (8 ));
SQL> insert into Table1 values ('weate ');
SQL> insert into Table1 values ('wether ');
SQL> insert into Table1 values ('gao ');

SQL> select XM from Table1 where soundex (XM) = soundex ('weate ');

XM
--------
Weather
Wether

14. Trim ('s 'from 'string ')
Leading
Trailing
If this parameter is not specified, the space character is used by default.

15. AbsReturns the absolute value of a specified value.
SQL> select ABS (100), ABS (-100) from dual;

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

16. ACOsReturns the arc cosine value.
SQL> select ACOs (-1) from dual;

ACOs (-1)
---------
3.1415927

17. AsinReturns the arcsin value.
SQL & gt; select asin (0.5) from dual;

Asin (0.5)
---------
. 52359878

18. atanReturns the arc tangent of a number.
SQL> select atan (1) from dual;

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

19. ceilReturns the smallest integer greater than or equal to the given number.
SQL> select Ceil (3.1415927) from dual;

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

20. CosReturns the cosine of a given number.
SQL> select cos (-3.1415927) from dual;

Cos (-1, 3.1415927)
---------------
-1

21. coshReturns the arc cosine of a number.
SQL> select cosh (20) from dual;

Cosh (20)
---------
242582598

22. ExpReturns the n root of the number E.
SQL> select exp (2), exp (1) from dual;

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

23. FloorReturns an integer to a given number.
SQL> select floor (2345.67) from dual;

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

24. lnReturns 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)
------------------

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 () mod)
---------------------------
1 0 2

27. PowerReturns 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. SignReturns 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. SinReturns the sine of a number.
SQL> select sin (1.57079) from dual;

Sin (1.57079)
------------
1

31. SighReturns the hyperbolic sine value.
SQL> select sin (20), sinh (20) from dual;

Sin (20) sinh (20)
------------------
.. 91294525 242582598

32. SQRTReturns the root of number n.
SQL> select SQRT (64), SQRT (10) from dual;

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

33. TanReturns 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 ('000000', 'yyymmm'), 2), 'yyymmm') from dual;

To_cha
------
200002
SQL> select to_char (add_months (to_date ('000000', 'yyymmm'),-2), 'yyymmm') 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 ('19-December-1999 ', '19-March-1999') mon_between from dual;

Mon_between
-----------
9
SQL> selectmonths_between (to_date ('192. 100', 'yyyy. Mm. dd'), to_date ('192. 100', 'yyyy. 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

41. sysdateUsed to obtain 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 FMT = 'mi ', it indicates that the minute is retained and the second is truncated.
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. chartorowidConvert character data type to rowid type
SQL> select rowid, rowidtochar (rowid), ename from Scott. EMP;

Rowid rowidtochar (rowid) ename
----------------------------------------------
Aaaafkaacaaaaeqaaa Smith
Aaaafkaacaaaaeqaab Allen
Aaaafkaacaaaaeqaac ward
Aaaafkaacaaaaeqaad Jones

0 2

This article from: http://hi.baidu.com/fancy_wly/blog/item/59a57fa9862714ff1e17a282.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.