Oracle PL/SQL functions) (1)

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;<! -- Dvnews_ad_begin -->

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. 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. 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)
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. 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 ')
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