Usage and results of Oracle SQL built-in functions

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. Cos returns 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)

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

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 () 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
 
 
43. Convert (C, dset, sset)

Convert the source string sset from one language character set to another destination dset Character Set

SQL> select convert ('strutz', 'we8hp ', 'f7dec') "Conversion" from dual;

Conver

------

Strutz

44. hextorawConverts a hexadecimal string to a binary string.

45. rawtohextConverts a binary string to a hexadecimal string.

46. rowidtocharConvert the rowid data type to the character type

47. to_char (date, 'format ')

SQL> select to_char (sysdate, 'yyyy/MM/DD hh24: MI: ss') from dual;

To_char (sysdate, 'yy

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

2004/05/09 21:14:41

48. to_date (string, 'format ')Converts a string to a date in Oracle.

49. to_multi_byteConverts a single-byte character in a string to a multi-byte character.

SQL> select to_multi_byte ('high') from dual;

To

--

High

50. to_number

Converts a given character to a number.

SQL> select to_number ('20140901') year from dual;

Year

---------

1999

51. bfilename (Dir, file)Specify an external binary file

SQL> insert into file_tb1 values(bfilename('lob_dir1', 'image1.gif '));

52. Convert ('x', 'desc', 'source ')Convert the source of the X field or variable to desc

SQL> select Sid, serial #, username, decode (command,

2 0, 'none ',

3 2, 'insert ',

4 3,

5 'select ',

6 6, 'update ',

7, 'delete ',

8 8, 'drop ',

9 'other') cmd from V $ session where type! = 'Background ';

Sid serial # username cmd

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

1 1 none

2 1 none

3 1 none

4 1 none

5 1 none

6 1 none

7 1275 none

8 1275 none

9 20 Gao select

10 40 Gao none

53. Dump (S, FMT, start, length)

The dump function returns a varchar2 value in the internal numeric format specified by FMT.

SQL> Col global_name for A30

SQL> Col dump_string for A50

SQL & gt; Set Lin 200

SQL> select global_name, dump (global_name, 1017,8, 5) dump_string from global_name;

Global_name dump_string

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

Oracle. World typ = 1 Len = 12 characterset = zhs16gbk: W, O, R, L, d

54. empty_blob () and empty_clob ()

Both functions are used to initialize fields of the big data type.

55. Greatest

Returns the maximum value in a group of expressions, that is, compare the encoding size of characters.

SQL> select greatest ('A', 'AB', 'ac') from dual;

Gr

--

AC

SQL> select greatest ('Ah', 'an', 'day') from dual;

Gr

--

Days

56. Least

Returns the minimum value in a group of expressions.

SQL> select least ('Ah', 'an', 'day') from dual;

Le

--

Ah

57. uid

Returns a unique integer that identifies the current user.

SQL> show user

The user is "Gao"

SQL> select username, user_id from dba_users where user_id = uid;

Username user_id

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

Gao 25

58. User

Returns the name of the current user.

SQL> Select User from dual;

User

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

Gao

59. userevn

Returns information about the current user environment. The opt can be:

Entryid, sessionid, terminal, isdba, lable, language, client_info, Lang, vsize

Isdba checks whether the current user is a DBA. If yes, true is returned.

SQL> select userenv ('isdba ') from dual;

Useren

------

False

SQL> select userenv ('isdba ') from dual;

Useren

------

True

Session

Returned session flag

SQL> select userenv ('sessionid') from dual;

Userenv ('sessionid ')

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

152

Entryid

Return session Population Sign

SQL> select userenv ('entryid') from dual;

Userenv ('entryid ')

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

0

Instance

Returns the flag of the current instance.

SQL> select userenv ('instance') from dual;

Userenv ('instance ')

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

1

Language

Returns the current environment variable.

SQL> select userenv ('language') from dual;

Userenv ('language ')

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

Simplified chinese_china.zhs16gbk

Lang

Returns the abbreviation of the current environment language.

SQL> select userenv ('lang ') from dual;

Userenv ('lang ')

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

ZHS

Terminal

Returns the identifier of the user's terminal or machine.

SQL> select userenv ('terminal') from dual;

Userenv ('termina

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

Gao

Vsize (X)

Returns the size (in bytes) of X.

SQL> select vsize (user), user from dual;

Vsize (User) User

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

6 System

60. AVG (distinct | all)

All indicates the average value for all values, while distinct only calculates the average value for different values.

Sqlwks> Create Table table3 (XM varchar (8), Sal number (7,2 ));

The statement has been processed.

Sqlwks> insert into table3 values ('gao', 1111.11 );

Sqlwks> insert into table3 values ('gao', 1111.11 );

Sqlwks> insert into table3 values ('zhu', 5555.55 );

Sqlwks> commit;

SQL> select AVG (distinct Sal) from Gao. table3;

AVG (distinctsal)

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

3333.33

SQL> select AVG (all Sal) from Gao. table3;

AVG (allsal)

-----------

2592.59

61. Max (distinct | all)

For maximum value, "all" indicates the maximum value for all values, and "distinct" indicates the maximum value for different values. For the same value, only one time is used.

SQL> select max (distinct Sal) from Scott. EMP;

 

Max (distinctsal)

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

5000

62. Min (distinct | all)

Minimum value. "All" indicates minimum value for all values, and "distinct" indicates minimum value for different values. The same value is used only once.

SQL> select Min (all Sal) from Gao. table3;

Min (allsal)

-----------

1111.11

63. stddev (distinct | all)

Evaluate standard deviation. "All" indicates evaluate standard deviation for all values, and "distinct" indicates evaluate standard deviation for different values only.

SQL> select stddev (SAL) from Scott. EMP;

Stddev (SAL)

-----------

1182.5032

SQL> select stddev (distinct Sal) from Scott. EMP;

Stddev (distinctsal)

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

1229.951

64. Variance (distinct | all)Covariance

SQL> select variance (SAL) from Scott. EMP;

Variance (SAL)

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

1398313.9

65. GroupIt is mainly used to calculate the number of a group.

SQL> select deptno, count (*), sum (SAL) from Scott. EMP group by deptno;

Deptno count (*) sum (SAL)

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

10 3 8750

20 5 10875

30 6 9400

66. HavingGrouping statistics plus restrictions

SQL> select deptno, count (*), sum (SAL) from Scott. EMP group by deptno having NT (*)> = 5;

Deptno count (*) sum (SAL)

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

20 5 10875

30 6 9400

SQL> select deptno, count (*), sum (SAL) from Scott. EMP having count (*)> = 5 group by TNO;

Deptno count (*) sum (SAL)

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

20 5 10875

30 6 9400

67. OrderUsed to sort and output the queried results.

SQL> select deptno, ename, Sal from Scott. EMP order by deptno, Sal DESC;

Deptno ename Sal

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

10 King 5000

10 Clark 2450

10 Miller 1300

20 Scott 3000

20 Ford 3000

20 Jones 2975

20 Adams 1100

20 Smith 800

30 Blake 2850

30 Allen 1600

30 Turner 1500

30 ward 1250

30 Martin 1250

30 James 950