Upper_Lower_Initcap

UPPER, LOWER, INITCAP

When you do not know whether the string in the table is in UPPER case letter or LOWER case letter or with INITCAP(initial capital) letter. Such as below, "SAM" is written in all upper case letter, "govind" is written in all lower case letter and other names are with initial capital letter.

Name	Dept	Salary	comm	State
Ram	Technical	1000	null	TX
SAM	Admin	1100	null	CA
Hari	mechanical	1200	null	TX
Raju	Helper	800	800	TX
govind	HR	1000	null	CA
Murali	Driver	900	null	NY
Murali1	DHead	null	null	NY
Sita	Actor	1400	111	TX

 

Below queries will not work:

Select * from TableUnitTest where name = 'Sam';   This will return no result because there is no "Sam".

Select * from TableUnitTest where name = 'sam';   This will return no result because there is no "sam".

Select * from TableUnitTest where name = 'Govind';   This will return no result because there is no "Govind".

Select * from TableUnitTest where name = 'GOVIND';   This will return no result because there is no "GOVIND".

Select * from TableUnitTest where name = 'RAM';   This will return no result because there is no "RAM".

Select * from TableUnitTest where name = 'ram';   This will return no result because there is no "ram".

Select * from TableUnitTest where name = 'HARI';   This will return no result because there is no "HARI".

Select * from TableUnitTest where name = 'hari';   This will return no result because there is no "hari".

 

Below Queries will work: 

Select * from TableUnitTest where UPPER(name) = 'RAM';

Select * from TableUnitTest where lower(name) = 'ram';

Select * from TableUnitTest where initcap(name) = 'Ram';  //No need of initcap here because it is already initcap in the table. But you will not know in the real time situation.

Select * from TableUnitTest where UPPER(name) = 'SAM'; //No need of Upper here because it is already UPPER in the table. But you will not know in the real time situation.

Select * from TableUnitTest where lower(name) = 'sam';

Select * from TableUnitTest where initcap(name) = 'Sam'; 

Select * from TableUnitTest where UPPER(name) = 'GOVIND';

Select * from TableUnitTest where lower(name) = 'govind'; //No need of Lower here because it is already Lower in the table. But you will not know in the real time situation.

Select * from TableUnitTest where initcap(name) = 'Govind'; 

Select * from TableUnitTest where initcap(name) = 'Hari';   

 

Some other examples:

Select UPPER("This is my bike.") from Dual ;    in Oracle

OR

Select UPPER("This is my bike.") ;  in MySQL

Output

THIS IS MY BIKE.

Select Lower("This Is My Bike.")  from Dual;

Output

this is my bike.

Select INITCAP("This is my bike.")  from Dual ;

Output:

This Is My Bike.