Common character string processing functions in Oracle SQL

Lowercase letters to uppercase letters: Upper ();

Uppercase to lowercase letters: lower ();

String truncation function: substr (STR, A, B );

A, B is an integer, STR is a string,

Truncates string 'str' from 'A' and searches from the right to the left when 'A' is-1,

Example: Select substr ('abc',) from dual;

--- Return BC;

String connection: |

Example: Select 'AB' | 'cd' from dual;

--- Return ABCD;

Character search function: instr (STR, a, B)

A is the character to be searched, B is the integer, STR is the string,

Searches for the position where a appears for the first time in the STR string. B indicates the number of digits in the STR string. It can be set to the default value,

Example: Select instr ('abcdce ', 'C') from dual;

-- 3 is returned;

Character replacement function: Replace (STR, a, B)

STR is a string, A is the character to be replaced, and B is the character to be replaced,

Example: Select Replace ('abcdce ', 'bc', 'test') from dual

-- Return atestdce

Oracle

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;

AA
ZeroSpace
------------------------------------
659748
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-', '123') |' to 23'Gao Qian's 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;
C1String to be searched
C2String to be searched
IStart position of the search. The default value is 1.
JThe location where it 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;

NameLength (name) ADDRLength (ADDR)
Sal length (to_char (SAL ))
---------------------------------------------------------------------------
Gao qicompeting3. Haidian District, Beijing6
9999.997

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

Aabbccdd
--------
Aabbccdd

8. Upper
Returns a string and upper-case all characters.
SQL> select upper ('abbccdd') Upper from dual;

Upper
--------
Aabbccdd

9. rpad and lpad (paste characters)
RpadPaste characters on the right of the column
LpadPaste 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
LtrimDelete the string on the left
RtrimDelete the string 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 ')
StringCharacter or variable to be replaced
S1String to be replaced
S2String 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 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 ')
LeadingTrim the previous character
TrailingRemove the following characters
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)
------------------
100100

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)
------------------
02

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 (10, 3)MoD (3, 3)MoD (2, 3)
---------------------------
102

27. Power
Returns the N2 root of N1.
SQL> select power (2, 10), power (3, 3) from dual;

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

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 (0, 123) sign (-100)Sign (0)
----------------------------
1-10

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)
---------------------------
100124.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 ('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 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_timeLos_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. sysdate
Used 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,
2To_char (trunc (sysdate, 'mi'), 'yyyy. Mm. dd hh24: MI: ss') hhmm from dual;

HHHhmm
--------------------------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

42. chartorowid
Convert character data type to rowid type
SQL> select rowid, rowidtochar (rowid), ename from Scott. EMP;

RowidRowidtochar (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. hextoraw
Converts a hexadecimal string to a binary string.

45. rawtohext
Converts a binary string to a hexadecimal string.

46. rowidtochar
Convert 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_byte
Converts 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,
20, 'none ',
32, 'insert ',
43,
5'Select ',
66, 'update ',
77, 'delete ',
88, 'drop ',
9'Other') cmdFrom v $ session where type! = 'Background ';

SidSerial # UsernameCMD
------------------------------------------------------
11None
21None
31None
41None
51