Common Oracle Functions

Single-record functions in SQL
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
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-', '000000') |' to 23' Gao Qian competing phone number from dual;

Gao Qian's 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', 'A', 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 ))
---------------------------------------------------------------------------
Gao Qian Jing 3 Beijing haiding District 6 9999.99 7

Common Oracle functions (1)
Single record function in SQL 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 322. the CHR gives an integer and returns the corresponding characters. SQL> select CHR (54740) Zhao, CHR (65) chr65 from dual; ZH c ---zhao a3.concat connects two strings; SQL> select Concat ('010-', '000000') |' to 23' Gao Qian competing phone number from dual; Gao Qian competing phone number -------------- 010-88888888 to 88888888. initcap returns the string and changes the first letter of the string Uppercase; SQL> select initcap ('Smith ') upp from dual; upp ----- smith5.instr (C1, C2, I, j) Search for the specified character in a string, returns the location where the specified character is found. the start position of the string C2 to be searched by C1, which is the position where the 1 J string appears by default, the default value is 1sql> select instr ('oracle traning', 'A', 1, 2) instring from dual; instring --------- 96. 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) ------ ------------ ---------------- ------------ --------- ---------------------- Gao Qian Jing 3 Beijing sea ingot Area 6 9999.99 7. lower returns a string and converts all characters into lowercase SQL> select lower ('aabbccdd') aabbccdd from dual; aabbccdd -------- aabbccdd8.upper returns a string, and all the characters in the upper case SQL> select upper ('aabbccdd') Upper from dual; UPPER--------AABBCCDD 9. rpad and lpad (paste character) rpad paste character on the right of the column lpad paste character on the left of the column SQL> select lpad (rpad ('gao', 10 ,'*'), 17, '*') from dual; lpad (RPA D ('gao', 1 ----------------- ********* Gao ******** fill up 10 characters. ltrim and rtrimltrim Delete the string on the left rtrim Delete the string on the right SQL> select ltrim (rtrim ('gao Qian jing', ''),'') from dual; ltrim (rtrim ('------------- Gao Qian jing11.substr (string, start, count) Take the sub-string, start from start, take count SQL> select substr ('123', 13088888888) from dual; substr ('-------- 0888888812. replace ('string', 's1', 's2') string the character to be replaced or the string S2 to be replaced by the variable S1 SQL> Sel ECT Replace ('He love you', 'hes', 'I') from dual; replace ('H ---------- I love you13.soundex returns a string with the same pronunciation as the 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 -------- weatherwether14.trim ('s 'from 'string') Leading cut the character traili If not specified, the default Delimiter is space character 15. ABS returns the absolute value of the specified value. SQL> select ABS (100), ABS (-100) from dual; ABS (100) ABS (-100) --------- ------- 100 10016. SQL> select ACOs (-1) from dual; ACOs (-1) --------- 3.141592717.asin SQL> select asin (0.5) from dual; asin (0.5 )---------. 5235987818. atan returns the arc tangent of a number. SQL> select atan (1) from dual; atan (1 )---------. 7853981619. ceil returns the smallest integer that is greater than or equal to the given number. SQL> select Ceil (3.1415927) from Dual; Ceil (3.1415927) --------------- 420. cos returns the cosine of a given number. SQL> select cos (-3.1415927) from dual; cos (-3.1415927) --------------121. cosh returns an SQL> select cosh (20) from dual; cosh (20) --------- 24258259822. exp returns the n root SQL of the number E> select exp (2), exp (1) from dual; exp (2) exp (1) --------- 7.3890561 2.718281823.floor integer SQL> select floor (2345.67) from dual; floor (2345.67) -------------- 234524. ln return SQL> select ln (1), Ln (2), Ln (2.7182818) from dual; ln (1) ln (2) ln (2.7182818) --------- ------------- 0. 69314718. 9999999925.log( N1, N2) returns a logarithm SQL> Select log (), log () from dual; log) --------- 0 226.mod( N1, N2) returns a remainder SQL> select Mod (), MOD () from dual; Mod) moD (3, 3) mod (2, 3) --------- 1 0 227. power returns N1 N2 times Explain SQL> select power (1024), power (2728) from dual; power. round and trunc round 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-5529. sign: returns the number n. If it is greater than 0, 1 is returned. If it is less than 0,-1 is returned. If it is equal to 0, 0 SQL> select sign (123), sign (-100), sign (0) is returned) from dual; sign (123) sign (-100) Sign (0) --------- ---------- --------- 1-1 030. sin returns the sine of a number. SQL> select sin (1.57079) from dual; sin (1.57079) ------------ 131. sigh returns the hyperbolic sine value SQL> select sin (20), sinh (20) from dual; sin (20) sinh (20 )------------------. 91294525 24258259832. SQRT returns the number N root SQL> select SQRT (64), SQRT (10) from dual; SQRT (64) SQRT (10) --------- 8 3.162277733.tan returns the tangent of a number. SQL> select Tan (20), Tan (10) from dual; Tan (20) Tan (10) --------- ------- 2.2371609. 6483608334. 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 intercepts a number of SQL> select trunc (124.1666,-2) trunc1, trunc (124.16666, 2) from dual; trunc1 trunc (124.16666, 2) based on the specified precision) --------- ------------------ 100 124.16 36. add_months add or subtract the month SQL> select to_char (add_months (to_date ('20170101', 'yyymmm'), 2), 'yyyy MM ') from dual; TO_CHA------200002SQL> select to_char (add_months (to_date ('000000', 'yyymmm'),-2), 'yyymmm') from dual; TO_CHA------19991037.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.10sql> select last_day (sysdate) from dual; last_day (s----------31-august-0438. months_between (date2, date1) Given the date2-date1 of the month SQL> select months_between ('19-December-100', '19-March-100') mon_between from dual; mon_between ----------- 9sql> selectmonths_between (to_date ('2017. 05.20 ', 'yyyy. mm. dd'), to_date ('2017. 05.20 ', 'yyyy. mm. dd') mon_betw from dual; mon_betw ----------6039. new_time (date, 'this', 'that ') returns the date and time SQL> select to_char (sysdate, 'yyyy. mm. dd hh24: MI: ss') bj_time, to_char (new_time 2 (sysdate, 'P DT ', 'gmt'), 'yyyy. mm. dd hh24: MI: SS ') los_angles from dual; bj_time los_angles ------------------- --------------------- 2004.05.09 11:05:32 2004.05.09 3240. next_day (date, 'day') calculates the date of the next week after the date and week X. SQL> select next_day ('18-January 1, May-2001 ', 'Friday ') next_day from dual; next_day----------25- May-01 41. sysdate is used to obtain the current system date SQL> select to_char (sysdate, 'dd-mm-yyyy Day') from dual; to_char (sysdate, '--------------- 0 Trunc (date, FMT) on Sunday truncated the date according to the given requirements. If FMT = 'mi ', the minute is retained, and the second SQL is truncated> 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 converts the character data type to the rowid type. SQL> select rowid, rowidtochar (rowid), ename from Scott. EMP; rowid rowidtochar (rowid) E Name ------------------ ---------- aaaafkaacaaaaeqaaa aaaafkaacaaeqaaa aaeqaaa aaaaaaaaaaeqaac restart jones43.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 ------ strutz44.hextoraw converts a hex string to a binary 45.r Awtohext converts a binary string to hexadecimal 46. rowidtochar converts 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 ------------------- 21:14:41 48. to_date (string, 'format') converts a string into a date 49 in Oracle. to_multi_byte converts a single-byte character in a string to a multi-byte character. SQL> select to_multi_byte ('high') from dual; To -- 50 high. to_number converts the given character to a numeric SQL> select to_number ('000000') year from dual; Year --------- 199951. bfilename (Dir, file) specifies an external binary file SQL> insert into file_tb1 values(bfilename('lob_dir1', 'image1.gif '); 52. convert ('x', 'desc', 'source') converts the source of the X field or variable to descsql> select Sid, serial #, username, decode (command, 2 0, 'none', 3 2, 'insert', 4 3, 5 'select', 6 6, 'update', 7 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
A A zero space --------- 65 97 48 32
2. the CHR gives an integer and returns the corresponding characters. SQL> select CHR (54740) Zhao, CHR (65) chr65 from dual;
Zh c ---zhao
3. Concat connects 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. 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; the start position of the string C2 to be searched by C1. The default value is 1j. The default value is 1sql> select instr ('oracle traning', 'A ', 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) ------ ------------------------ ------------ --------- -------------------- Gao Qian Jing 3 Beijing Haijin District 6 9999.99 7

7. lower returns a string and converts all characters into lowercase SQL> select lower ('aabbccdd') aabbccdd from dual;
Aabbccdd -------- aabbccdd
8. Upper returns a string, and uppercase all the characters. SQL> select upper ('abbccdd') Upper from dual;
UPPER--------AABBCCDD

9. rpad and lpad (paste character) rpad paste character on the right of the column lpad paste character on the left of the column SQL> select lpad (rpad ('gao', 10 ,'*'), 17, '*') from dual;
Lpad (rpad ('gao', 1 ----------------- ********** Gao ******** is not enough characters to fill *
10. ltrim and rtrimltrim Delete the left-side string rtrim Delete the right-side string SQL> select ltrim (rtrim ('gao Qian jing', ''),'') from dual;
Ltrim (rtrim ('----------- Gao Qian Jing
11. substr (string, start, count) Take the sub-string, starting from start, take count SQL> select substr ('123456', 13088888888) from dual;
Substr ('-------- 08888888
12. replace ('string', 's1', 's2 ') string the character to be replaced or the string S2 variable S1 to be replaced SQL> select Replace ('He love you', 'hes', 'I') from dual;
Replace ('H ---------- 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 ('weate '); SQL> insert into Table1 values ('wether '); SQL> insert into Table1 values ('gao ');
SQL> select XM from Table1 where soundex (XM) = soundex ('weate ');
XM -------- weatherwether
14. Trim ('s 'from 'string') Leading: trim the character trailing. If not specified, it is a space character by default.
15. Abs returns the absolute value of the 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> select asin (0.5) from dual;
Asin (0.5) ---------. 52359878
18. atan returns an arc tangent SQL> select atan (1) from dual;
Atan (1) -----------. 78539816
19. The return value of Ceil is 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 digital arc cosine value SQL> select cosh (20) from dual;
Cosh (20) ----------- 242582598
22. Exp returns the n root SQL> select exp (2), exp (1) from dual;
Exp (2) exp (1) --------- 7.3890561 2.7182818
23. Floor returns an integer to the 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 (2.7182818) --------- ------------- 0. 69314718. 99999999
25. Log (N1, N2) returns a logarithm SQL with N1 as the base N2> Select log (), log () from dual;
Log () log () --------- 0 2
26. Mod (N1, N2) returns a remainder of N1 divided by N2 SQL> select Mod (), MOD (), MOD () from dual;
MoD () mod () --------- 1 0 2
27. Power returns the N2 power root SQL of N1> select power (), power () from dual;
Power (1024) power () ----------- ---------- 27
28. Round and trunc round 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 the number n. If it is greater than 0, 1 is returned. If it is less than 0,-1 is returned. If it is equal to 0, 0 SQL> select sign (123), sign (-100), sign (0) is returned) 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 number N root SQL> select SQRT (64), SQRT (10) from dual;
SQRT (64) SQRT (10) --------- 8 3.1622777
33. Tan returns the tangent of the 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 intercepts a number of SQL statements according to the specified precision> 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 the month SQL> select to_char (add_months (to_date ('20170101', 'yyyymm'), 2), 'yyyymm') from dual;
TO_CHA------200002SQL> select to_char (add_months (to_date ('201312', '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.10sql> select last_day (sysdate) from dual;
Last_day (s----------31-august-04
38. months_between (date2, date1) Given the date2-date1's month SQL> select months_between ('19-December-100', '19-March-100') mon_between from dual;
Mon_between ----------- 9sql> selectmonths_between (to_date ('2017. 05.20 ', 'yyyy. mm. dd'), to_date ('2017. 05.20 ', 'yyyy. mm. dd') mon_betw from dual;
Mon_betw ----------60
39. new_time (date, 'this', 'that ') returns the date and time 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') calculates the date of the next week after the date and week X. SQL> select next_day ('18-January 1, May-2001 ', 'Friday ') next_day from dual;
Next_day----------25-May-01

41. sysdate is used to obtain the current system date 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', the score is retained, 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 converts the character data type to rowid type SQL> select rowid, rowidtochar (rowid), ename from Scott. EMP;
Rowid rowidtochar (rowid) ename ------------------ ------------ aaaafkaacaaaaeqaaa aaaafacaaaaeqaaa aaaafaakaacaaaaeqaab aaaafkaacaaaaeqaac ~jones
43. convert (C, dset, sset) converts the source string sset from one language character set to another dset Character Set SQL> select convert ('strutz', 'we8hp ', 'f7dec ') "Conversion" from dual;
Conver ------ strutz
44. hextoraw converts a hex string to a binary string.
45. rawtohext converts a binary string to a hexadecimal string.

46. rowidtochar converts 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 ----------------- 21:14:41

48. to_date (string, 'format') converts a string to a date in Oracle.
49. to_multi_byte converts single-byte characters in a string into multi-byte characters. SQL> select to_multi_byte ('high') from dual;
To -- high
50. to_number converts the given character to a numeric SQL> select to_number ('000000') year from dual;
Year --------- 1999
51. bfilename (Dir, file) specifies an external binary file SQL> insert into file_tb1 values(bfilename('lob_dir1', 'image1.gi '));
52. convert ('x', 'desc', 'source') converts the source of the X field or variable to descsql> select Sid, serial #, username, decode (command, 2 0, 'none', 3 2, 'insert', 4 3, 5 'select', 6 6, 'update', 7 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