Character Manipulation Function - (CONCAT, LENGTH, REPLACE,REVERSE, TRANSLATE, LPAD, RPAD, LTRIM, RTRIM, TRIM, SUBSTR, INSTR)

1) CONCAT

This command accepts exactly two arguments and displays the result by concatenating the two arguments.

Example

SELECT CONCAT(FIRST_NAME, LAST_NAME) FROM EMPLOYEES;

CONCAT(FIRST_NAME,LAST_NAME)
---------------------------------------------
EllenAbel
SundarAnde
MozheAtkinson
DavidAustin

2) LENGTH

This command can accept only one argument and displays the length of the string.

Example

SELECT LENGTH(FIRST_NAME) FROM EMPLOYEES;

LENGTH(FIRST_NAME)
------------------
                 5
                 6
                 5
                 5
                 7

3) REPLACE

This command can accept exactly two or three arguments and helps to replace the character.

REPLACE(FIRST_NAME,'e','x') – Here ‘e’ is replaced with ‘x’.

REPLACE(FIRST_NAME,'e') – Here ‘e’ is removed.

Example

SELECT FIRST_NAME, REPLACE(FIRST_NAME,'e','x') FROM EMPLOYEES;

FIRST_NAME           REPLACE(FIRST_NAME,'
--------------------          ----------------------------------
Ellen                          Ellxn
Sundar                      Sundar
Mozhe                       Mozhx
David                         David
Hermann                   Hxrmann

SELECT FIRST_NAME, REPLACE(FIRST_NAME,'e') FROM EMPLOYEES;

FIRST_NAME           REPLACE(FIRST_NAME,'
--------------------          ----------------------------------
Ellen                          Elln
Sundar                      Sundar
Mozhe                       Mozh
David                         David
Hermann                   Hrmann

4) REVERSE

This command accepts exactly one argument and displays the string in reverse order.

Example

SELECT REVERSE(SNAME) FROM STUDENT;

REVERSE(SNAME)
--------------------
MARIHBA
AKLA
AHSID
AHSE
TEEMNAM

5) TRANSLATE

This command replaces the sequence of string with another set of character. For example, it replaces the 1st character from string to replace with the character from replacement string then the 2nd character from string to replace with the 2nd character from replacement string so on.

Example

SELECT TRANSLATE('1ABC23','123','456') FROM DUAL;

TRANSL
------
4ABC56

6) LPAD

This command can accept exactly two or three arguments and
LPAD (SNAME,10,'@') – here if the string less than 10 characters then remaining character is filled by ‘@’ in the left-hand side and displays exact 10 characters.

LPAD(SNAME,5) – Here it displays the string only up to 5 characters.

Example

SELECT LPAD(SNAME,10,'@') FROM STUDENTS;

LPAD(SNAME,10,'@')
----------------------------------------
@@@ABHIRAM
@@@@@@ALKA
@@@@@DISHA
@@@@@@ESHA

SELECT LPAD(SNAME,5) FROM STUDENTS;

LPAD(SNAME,5)
--------------------
ABHIR
ALKA
DISHA
ESHA

7) RPAD

This command can accept exactly two or three arguments and

RPAD (SNAME,10,'@') – here if the string less than 10 characters then remaining character is filled by ‘@’ in right-hand side and displays exact 10 characters.

RPAD(SNAME,5) – Here it displays the string only up to 5 characters.

Example

 SELECT RPAD(SNAME,10,'@') FROM STUDENTS;

RPAD(SNAME,10,'@')
----------------------------------------
ABHIRAM@@@
ALKA@@@@@@
DISHA@@@@@
ESHA@@@@@@

 SELECT RPAD(SNAME,5) FROM STUDENTS;

RPAD(SNAME,5)
--------------------
ABHIR
ALKA
DISHA
ESHA

8) LTRIM

This command is used to remove special character or character on the left-hand side.

Example

SELECT LTRIM('****HELLO**WORLD****','*') FROM DUAL;

LTRIM('****HELLO
----------------

HELLO**WORLD****

9) RTRIM

This command is used to remove special character or character on the right-hand side.

Example

SELECT RTRIM('****HELLO**WORLD****','*') FROM DUAL;

RTRIM('****HELLO
----------------
****HELLO**WORLD

10) TRIM

This command is used to remove special character or character on both the left & right-hand side.

Example

SELECT TRIM('*' FROM '****HELLO**WORLD****') FROM DUAL;

TRIM('*'FROM
------------
HELLO**WORLD

11) SUBSTR

This command is used to get the string of particular length from the particular starting position.

SUBSTR(SNAME,1,3) – It is used to print 3 characters starting from position 1.

SUBSTR(SNAME,-3) – it is used to print 3 characters starting from the last position.

Example

 SELECT SUBSTR(SNAME,1,3) FROM STUDENTS;

SUBSTR(SNAME
------------
ABH
ALK
DIS
ESH

SELECT SUBSTR(SNAME,-3) FROM STUDENTS;

SUBSTR(SNAME
------------
RAM
LKA
SHA
SHA

12) INSTR

This command can accept two to four arguments. It is used to find the position of occurrence of a specified character.

Syntax

INSTR(STRING1, STRING 2, STARTING POSITION, Nth APPEARANCE)

Example

 SELECT INSTR('TECH ON THE NET','E') FROM DUAL;

INSTR('TECHONTHENET','E')
-------------------------
                        2

SELECT INSTR('TECH ON THE NET','E',1,2) FROM DUAL;

INSTR('TECHONTHENET','E',1,2)
-----------------------------

                           11
13) ASCII

This command returns ASCII values for the specified character. If you declare a word then it returns the ASCII values for the 1st character.

Example

SELECT ASCII('C') FROM DUAL;

ASCII('C')
----------
        67