Character Function in SQL

Character Functions are two types

1. Case Manipulation Functions:
   • UPPER
   • LOWER
   • INITCAP
2. Character Manipulation Functions:
   • CONCAT
   • SUBSTR
   • LENGTH
   • INSTR
   • LPAD / RPAD
   • TRIM
   • REPLACE

---

I . Case Manipulation Functions:

This functions convert case for character strings

Function	Output
LOWER (‘ CODEFAME SQL ‘)	codefame sql
UPPER (‘ codefame sql ‘)	CODEFAME SQL
INITCAP  (‘ codefame sql ‘)	Codefame Sql

Query : SELECT  LOWER(‘ HELLO WORLD’) from dual ;

Output : hello world

Query : SELECT ename, LOWER (ename) from emp;

---

II .Character Manipulation Functions:

These functions manipulate character strings:

CONCAT : CONCAT functions holds two arguments

Types :

• Normal form
• nested form

Normal Form  Syntax:

SELECT  F1( arg1, arg2 ) from tablename;

SELECT  F1( arg1 , F2( arg2, arg3) from tablename;

SELECT  F1( arg1 , F2( arg2, F3( arg3 , arg4 ) ) ) from tablename ;

Example For Normal Form CONCAT : 

Question :  Display  ” smith your salary is $800 “  by using concat function.

Query : 

SELECT  CONCAT ( ename, CONCAT ( ‘Your Salary is $’, CONCAT (  ‘ ‘ , CONCAT( ‘ ‘ , sal ))))

FROM emp WHERE ename =’SMITH’;

Question :  Display  “Hi  smith  i am learning SQL from CodeFame and my salary is $20000 “  by using concat function.

Query : 

SELECT  CONCAT ( ‘Hi’, CONCAT ( ename, CONCAT (  ‘ i am learning SQL from CodeFame and my salary is $ ‘ , CONCAT( ‘ ‘ , sal ))))  FROM emp WHERE ename =’SMITH’;

Nested  Form  Syntax:

SELECT   F3(  F2(  F1(  arg1, arg2 ) , arg 3 ) , arg4 ) from tablename;

Query :

SELECT CONCAT(  CONCAT(  CONCAT( ename, ‘ ‘ ), ‘is working for ‘, ‘  ‘ ), deptno )

FROM  emp WHERE ename =’smith’;