Oracle Single-line character function parsing

1, ASCII (C)

Description: Returns the first character of C in the ASCII code corresponding to the decimal

Example:

Sql>select ASCII (' a ') a,ascii (' A ') b,ascii (' 0 ') c,ascii (') D from DUAL;

A B C D

-------------------- ---------- ----------

65 97 48 32

2, CHR (N)

Description: Returns the decimal ASCII code n corresponding character

Example:

Sql>select CHR (A,CHR) B,CHR (a) c,chr (a) D from DUAL;

A B CD

- - --

A A 0

3, CONCAT (C1,C2)

Description: Returns a String that is formed after adding C2 to the C1. Returns C2 if the C1 is null, or C1 if C2 is null, and returns NULL if both C1 and C2 are null

Example:

Sql>select CONCAT (' 010-', ' 8888 ') A,concat (NULL, ' 8888 ') b,concat (' 010-', NULL) c,concat (null,null) D from DUAL;

A B C D

------------ ---- -

010-88888888 010-

4, Initcap (C)

Description: Returns a string in which each first character in C is capitalized and all other characters are lowercase. Spaces between words, controlling character and punctuation

Example:

Sql>select Initcap (' You are ') A from DUAL;

A

-------

Youare

5, INSTR (C1,c2[,n[,m])

Note: In C1, start with N to search for the position of C2, and return the position number. If n is a negative number, the search moves from right to left, but the position numbers are still counted from left to right. N and M are all 1 by default

Example:

Sql>select INSTR (' Aabbccddee ', ' cc ') a,instr (' Aabbccddee ', ' cc ', 5) b,instr (' AABBCCDDEECC ', ' cc ', 1,2) c,instr (' Aabbccddee ', ' cc ', -6 D from DUAL;

A B C D

-------------------- ---------- ----------

5 5 11 5

6, LENGTH (C)

Description: Returns the length of C. Include all suffix spaces, or null if C is an empty string or null

Example:

sql> SELECT LENGTH (' AABBCCDD ') a,length (') b,length (NULL) C from DUAL;

A B C

---------- ---------- ----------

8

7, LOWER (C)

Description: Returns the string after all characters in C are lowercase

Example:

Sql>select LOWER (' AABBCCDD ') A from DUAL;

A

--------

Aabbccdd

8, Lpad (C1,N[,C2])

Description: Fills the C2 on the left side of the C1 until the total length of the string reaches N. The default value for C2 is a space. Returns the n characters to the left of the C1 if the length of the C1 is greater than n

Example:

Sql>select lpad (' AABBCC ', 8) a,lpad (' AABBCC ', 4) b,lpad (' AABBCC ', 8, ' 0 ') C from DUAL;

A B C

------------ --------

AABBCC AABB 00AABBCC

9, LTRIM (C1[,C2])

Note: Remove any characters from the C2 contained in the left side of the C1, and then return the remaining strings when you encounter a word that is not in C2 characters end. C2 Default to Spaces

Example:

Sql>select LTRIM (' Aabb ') A,ltrim (' 00aabb00 ', ' 0 ') B from DUAL;

A B

------------

AABB aabb00

10, NANVL (X,value)

Description: This function only works on Binary_float and binary_double. Returns value if X matches Nan (that is, not a number), otherwise X

For example (Pl/sql cannot be performed and must be performed in Sqlplus):

Sql>createtable Nanvl_demo (Dec_numnumber (10,2), bin_doublebinary_double, bin_floatbinary_float);

Table has been created.

Sql>insertinto nanvl_demovalues (0, ' nan ', ' nan ');

1 lines have been created.

Sql>select*from Nanvl_demo;

Dec_num bin_double Bin_float

---------- ---------- ----------

0 Nan Nan

Sql>select BIN_FLOAT,NANVL (bin_float,0) from Nanvl_demo;

BIN_FLOATNANVL (bin_float,0)

---------- ------------------

Nan 0

11, NVL (X,value)

Note: If x is empty, return value, otherwise return x

Example:

Sql>select NVL (', ' Aabb ') A,NVL (NULL, ' Aabb ') b,nvl (' AA ', ' Aabb ') C from DUAL;

A B C

-------- --

Aabbaabb AA