Single-Line Character functions of ORACLE Functions

1. ASCII (C)

Returns the corresponding decimal number of the first character of C in the ASCII code.

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)

Returns the characters corresponding to the decimal ASCII code N.

Example:

SQL> select chr (65) A, CHR (97) B, CHR (48) C, CHR (32) D FROM DUAL;

A B CD

----

A a 0

3. CONCAT (C1, C2)

Description: return the string formed after C2 is added to C1. If C1 is NULL, C2 is returned. If C2 is NULL, C1 is returned. If C1 and C2 are both NULL, NULL is returned.

Example:

SQL> select concat ('010-', '000000') A, CONCAT (NULL, '000000') 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 with uppercase letters for each first character of C and lowercase letters for all other characters. Use spaces between words to control the division between characters and punctuation marks

Example:

SQL> SELECT INITCAP ('you are ') A FROM DUAL;

A

-------

YouAre

5. INSTR (C1, C2 [, N [, M])

Note: In C1, search for the location where C2 appears at the nth time starting from N and return the number of this location. If N is a negative number, the search proceeds from right to left, but the position number is still calculated from left to right. N and M are both 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: return the length of C. Contains all suffixes and spaces. If C is a NULL string or NULL, NULL is returned.

Example:

SQL> select length ('abbccdd') A, LENGTH ('') B, LENGTH (NULL) C FROM DUAL;
A B C
------------------------------
8

7. LOWER (C)

Description: returns the string after all the characters in C are lowercase.

Example:

SQL> SELECT LOWER ('abbccdd') A FROM DUAL;

A

--------

Aabbccdd

8. LPAD (C1, N [, C2])

Note: Fill C2 on the left of C1 until the total length of the string reaches N. The default value of C2 is space. If the length of C1 is greater than N, the return value is N characters on the left of C1.

Example:

SQL> select lpad ('aabbcc ', 8) A, LPAD ('aabbcc', 4) B, LPAD ('aabbcc ', 8, '0') C FROM DUAL;

A B C

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

Aabbcc aabb 00 aabbcc

9. LTRIM (C1 [, C2])

Note: Remove any character in C2 on the left of C1. It ends when it is not a character in C2 and returns the remaining string. C2 is a space by default.

Example:

SQL> select ltrim ('aab') A, LTRIM ('00aabb00', '0') B FROM DUAL;

A B

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

Aabb aabb00

10. NANVL (X, value)

Note: This function only applies to BINARY_FLOAT and BINARY_DOUBLE. If X matches NaN (not a number), value is returned; otherwise, X is returned.

Example (PL/SQL cannot be executed and must be executed in SQLPLUS ):

SQL> CREATETABLE NANVL_demo (dec_numNUMBER (10, 2), bin_doubleBINARY_DOUBLE, bin_floatBINARY_FLOAT );

The table has been created.

SQL> INSERTINTO NANVL_demoVALUES (0, 'nan ', 'nan ');

One row has been created.

SQL> SELECT * FROM NANVL_demo;

DEC_NUM BIN_DOUBLE BIN_FLOAT

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

0 Nan

SQL> SELECT bin_float, NANVL (bin_float, 0) FROM NANVL_demo;

BIN_FLOATNANVL (BIN_FLOAT, 0)

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

Nan 0

11. NVL (X, value)

Description: If X is null, value is returned. Otherwise, X is returned.

Example:

SQL> select nvl ('', 'aabb ') A, NVL (NULL, 'aabb') B, NVL ('A', 'aabb ') C FROM DUAL;

A B C

----------

Aabbaabb aa

12. NVL2 (X, value1, value2)

NOTE: If X is null, value1 is returned; otherwise, value2 is returned.

Example:

SQL> SELECT NVL2 ('', 'aabb ', 'cdd') A, NVL2 (NULL, 'aabb', 'cdd') B, NVL2 ('A ', 'aab', 'cdd') c from dual;

A B C

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

Ccddccdd aabb

13. REPLACE (C1, C2 [, C3])

Note: replace C2 in C1 with C3, and then return the remaining string. C3 is NULL by default. If C3 is NULL, all characters where C2 appears are deleted. If C2 is NULL, C1 is returned. If C1 is NULL, NULL is returned.

Example:

SQL> select replace ('aabbccaab', 'A', '00') A, REPLACE ('aabbccaab', 'A') B, REPLACE ('aabbccaab', NULL) c from dual;

A B C

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

00bbcc00bbbbccbb aabbccaabb

14. RPAD (C1, N [, C2])

Note: Fill C2 on the right side of C1 until the total length of the string reaches N. The default value of C2 is space. N is the length of the returned bytes. If the number is shorter than the length of the original string, the RPAD function truncates the string into N characters from left to right.

Example:

SQL> select rpad ('aabbcc ', 8) A, RPAD ('aabbcc', 4) B, RPAD ('aabbcc ', 8, '0') C FROM DUAL;

A B C

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

Aabbcc aabb aabbcc00

15. RTRIM (C1 [, C2])

Note: Remove any character in C2 on the Right of C1. When it is not a character in C2, it ends and returns the remaining string. C2 is a space by default.

Example:

SQL> select rtrim ('aab') A, RTRIM ('00aabb00', '0') B FROM DUAL;

A B

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

Aabb 00 aabb

16. SUBSTR (C, M [, N])

Description: return the substring of C, where M is the starting position of the substring and N is the length of the substring. If M is 0, it starts from the first character of C. If M is negative, it starts from the specified position at the end of C.

Example:

SQL> select substr ('aabbccdd', 3) A, SUBSTR ('aabbccdd',) B, SUBSTR ('aabbccdd',-) C FROM DUAL;

A B C

----------

Bbccddbb cd

17. TRANSLATE (string, from_str, to_str)

Note: The Return Value replaces each character in from_str with a string after the corresponding character in to_str. TRANSLATE Is a superset of the functions provided by REPLACE. If from_str is longer than to_str, extra characters in from_str instead of to_str will be deleted from the string because they do not have replacement characters. To_str cannot be blank. Oracle interprets the NULL String as NULL, and if any parameter in the TRANSLATE Is NULL, the result is also NULL.

Example:

SQL> select TRANSLATE ('abbcc ', 'aab', '000000') A, TRANSLATE ('abcbbaadef', 'ba', '# @') B, TRANSLATE ('22aabbcc22', '0123456789abcdefghijklmnopqrstuvwxyz ', '123') Cfrom dual;

A B C

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

0011cc @ # c ###@ def 2222

18. TRIM ([C1] C2 FROM C3)

Note: C1 is reserved for Yu. The following strings can be used: LEADING, TRAILING, and BOTH. C1 must be used with FROM. Start from C1 of the C3 string, delete the C2 character, and return the remaining C3 string. C1 is BOTH by default. C2 is a space by default. If either of C1, C2, and C3 is NULL, NULL is returned.

Example:

SQL> select trim ('aabb ') A, TRIM (LEADING '0' FROM '00aabb00') B, TRIM (TRAILING '0' FROM '00aabb00') C, TRIM (BOTH '0' FROM '00aabb00') d fromdual;

A B C D

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

Aabbaabb00 00 aabb

19. UPPER (C)

Description: returns the string after all the characters in C are capitalized.

Example:

SQL> SELECT UPPER ('abbccdd') A FROM DUAL;

A

--------

AABBCCDD