March 29th, 2009 
admin Sql Case Function :
- The CASE function allows you to evaluate a column value on a row against multiple criteria, where each criterion might return a different value
- CASE is just a searched (or lookup) expression – you cannot RETURN from inside it – it’s kind of like IF() in Excel
Sql Case Function has two Types.
- Simple Function
- Searched Function
Simple Function :
Syntax :
when expression then Result
when expression then Result
else Result
Example for Simple Function:
Select
state,
case @state
when ‘IND’ then ‘INDIA’
when ‘PAK’ then ‘Pakistan’
when ‘RSA’ then ‘South Africa’
when ‘Lanka’ then ‘Sri Lanka’
end as State Name
from State_name
Result:
State State_name
==== =========
IND INDIA
Searched Function :
Syntax :
when boolean_expression then Result
when boolean_expression then Result
else Result
Example for Searched Function:
Code State
=== ====
1 I
2 IND
3 PAK
4 RSA
5 SL
Select
state,
case
when state in (‘IND’,'I’) then ‘INDIA’
when state in (‘PAK’,'P’) then ‘Pakistan’
when state in (‘RSA’,'SA’) then ‘South Africa’
when state in (‘Lanka’,'SL’) then ‘Sri Lanka’
end as State Name
from State_name
Result :
State State Name
==== =========
I INDIA
IND INDIA
PAK Pakistan
RSA South Africa
SL Sri Lanka
Popularity: 46% [?]
Posted in 
Tags: 
