Character Manipulation Function

CHARACTER MANIPULATION FUNCTIONS

LENGTH()
CONCAT()
REVERSE()
SUBSTR()
INSTR()
LPAD()
RPAD()
LTRIM()
RTRIM()
REPLACE()
TRANSLATE()

LENGTH()

SQL> Select First_name, length(first_name) from employees;

SQL> Select First_name, length(first_name) from employees where length(first_name)=5;

REVERSE(): 

This function used to reverse the given input.

SQL> Select upper(reverse(first_name))"UPPER REVERSE" from employees;

UPPER REVERSE
------------
LEUNAM ESOJ
RETEP
ARALC
ATNAHS
ANALA
WEHTTAM
REFINNEJ
INELE

SQL> Select Reverse('12345') from dual;

Reverse
-------
54321

SUBSTR(): 

SUBSTR returns a specified portion of a string
Eg SUBSTR('BCDEF',4)
output BCDE


Select first_name
      ,substr(first_name,'1','3')"substr1"
      ,substr(first_name,'2','3')"substr2"
      ,substr(first_name,'6','3')"substr3"
      ,substr(first_name,'4')"substr4"
      ,substr(first_name,'-3','2')"substr5"
from employees;

Note: 
substr(first_name,'1','3')"substr1"
Here 1 is starting position
         3 is upto 3 char to starting position

substr(first_name,'4')"substr4"
Here 4 is starting position there is no ending position so it will display till the end.

substr(first_name,'-3','2')"substr5"
Here -3 denotes starting char from the last so -3(a) is starting position.

INSTR():

INSTR provides character position in which a pattern is found in a string.
Eg INSTR('ABC-DC-F','-',2)
output 7 (2nd occurence of '-')

SQL>  Select last_name
      ,Instr(last_name,'a')"instr1"
      ,Instr(last_name,'a',2,3)"instr2"
      ,Instr(last_name,'a',8,1)"instr3"
      ,Instr(last_name,'a',8,2)"instr4"
      ,Instr(last_name,'a',5)"instr5"
from employees where lower(last_name)=lower('pataballa')

Note: 
Instr(last_name,'a')"instr1"
Here a is the pattern Its defaultly search the first occurance position its 2

Instr(last_name,'a',2,3)"instr2"
Here a is the pattern 2 is starting position 3 is denoted as 3 occurance position 6

Instr(last_name,'a',8,1)"instr3"
Here a is pattern 8 is starting position 1 is 1st occurance position its 9

Instr(last_name,'a',8,2)"instr4"
Here a is pattern 8 is starting position 2 is 2nd occurance after the starting point but

there is no a's in second occurance so its 0
   
Instr(last_name,'a',5)"instr5"
Here a is pattern 5 is starting position there is no occurance specified so it takes after

the 5 th position its 6

Example :

SQL>  Select last_name
      ,Instr(last_name,'e',2,1)"instr2"
      ,Instr(last_name,'e',2,2)"instr2"
      from employees
where Instr(last_name,'e',2,1)<>0
and Instr(last_name,'e',2,2)=0;

Here the where condition specifies the not equal to zero and equal to zero so the output is

LPAD and RPAD

LPAD : To adjust the left hand side padding of charecters,numbers.
RPAD : To adjust the right hand side padding of charecters, numbers.

Select first_name
     ,lpad(salary,8,'0') "Sal1"
     ,rpad(salary,8,'$') "Sal2"
from employees;

Here lpad(salary,8,'0')
8 denotes charecters or numbers we need so total length is 8.
0 denotes the remaining charcters filled with zeros in left hand side.
Here rpad(salary,8,'$')
8 denotes charecters or numbers we need so total length is 8.
$ denotes the remaining charcters filled with zeros in Right hand side.

LTRIM/RTRIM

LTRIM: Trim the charecters and numbers in left hand side.
RTRIM: Trim the charecters and numbers in Right hand side.

select '000001234500000' "Text"
       ,Ltrim( ' 000001234500000','0') "LTRIM"
       ,Rtrim( ' 000001234500000','0')  "RTRIM"
       ,trim ('0' from '000001234500000')"TRIM"
from dual;

REPLACE AND TRANSLATE

Both Replace and Translate are single row functions in Oracle 9i. The Replace Function
replaces single character with multiple characters. But in Translate Function replaces
single character with single character only.

select 'welcome' "Text"
     , replace('welcome','l','xyzcvb') "Replace"
     , translate('welcome','l','xymkij') "Translate"
from dual;

Here Replace l with multiple characters is changed but translate with  multiple characters
not changed because translate function work only one character at a time.