Oracle functions [string functions, mathematical functions, date functions] Page 1/4

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

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