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
Give an integer and return the corresponding character;
Sql> Select Chr (54740) ZHAO,CHR () chr65 from dual;
En C
-- -
Zhao A
3.CONCAT
Connect two strings;
Sql> Select Concat (' 010-', ' 88888888 ') | | Turn 23 ' Gao Hu bid from dual;
Gao Hu Phone
----------------
010-88888888 ext. 23
4.INITCAP
Returns a string and turns the first letter of the string into uppercase;
Sql> Select Initcap (' Smith ') Upp from dual;
UPP
-----
Smith
5.INSTR (C1,C2,I,J)
Searches for a specified character in a string, returning the location of the specified character;
C1 the string being searched
C2 the string you want to search for
I the start of the search, the default is 1
The position of J appears, default is 1
Sql> Select InStr (' Oracle traning ', ' ra ', 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 Hu 3 Beijing Sea ingot Area 6 9999.99 7
7.LOWER
Returns a string and converts all characters to lowercase
Sql> Select lower (' AABBCCDD ') AABBCCDD from dual;
Aabbccdd
--------
Aabbccdd
8.UPPER
Returns a string and capitalizes all characters
Sql> Select Upper (' AABBCCDD ') upper from dual;
UPPER
--------
Aabbccdd
9.RPAD and Lpad (paste characters)
Rpad paste characters to the right of the column
Lpad paste characters to the left of the column
Sql> Select Lpad (Rpad (' Gao ', ten, ' * '), "*") from dual;
Lpad (Rpad (' GAO '), 1
-----------------
gao*******
Not enough characters to fill with *
10.LTRIM and RTrim
LTRIM deletes the string appearing on the left
RTRIM Delete the string that appears on the right
Sql> Select LTrim (RTrim (' Gao Qian Jing ', '), ') from dual;
LTRIM (RTRIM ('
-------------
Gao Qian Jing
Current 1/4 page
1234 Next read the full text