Character Manipulation Function – Length,Trim,Replace,Alignment in SQL

Posted on by

LENGTH : 

  • It will display the length.

Query: SELECT  LENGTH( ename ) from emp;

Output:   Smith       5

Question: Display the employee whose name has exactly 6 character

Query: SELECT  ename  FROM  emp WHERE  length( ename ) = 6;

TRIM :

  • it is used to trim the character presenting starting and ending position of the string.
  • it will not remove the character present in between the character.
  • it will include space as one character.
  • the arguments are case sensitive.

TRIM( ‘h’ from ‘hello’)

Example: 

Query : SELECT   TRIM( ‘H’  from  ‘HHHHHELLOWORLDHHHH’) FROM dual;

Output :  ELLOWORLD

REPLACE : 

  • it is used to replace the existing string.
  • it will have minimum 2 argument and maximum three argument.
  • it change the character according to the 3rd argument
  • spaces also get replaced.
  • REPLACE ( ‘HELLO WORLD’ , ‘L’ , ‘ * ‘)

Query : SELECT  REPLACE ( ‘HELLO WORLD’ , ‘L’ , ‘ * ‘)  from dual;

Output :  HE**O WOR*D

RPAD and LPAD :

  • it is used for alignment in SQL
  • LPAD ( ‘ HELLO ‘ , 6 , ‘ * ‘ )
  • RPAD ( ‘ HELLO ‘ , 6 , ‘ * ‘ )

Query :  SELECT  LPAD ( ‘ HELLO ‘ , 6 , ‘ * ‘ )  from dual table;

Output :  ******HELLO

Query : SELECT  LPAD ( ‘ HELLO ‘ , 6 , ‘ * ‘ )  from dual table;

Output :  HELLO******