CASE function in SQL Server 2005 – part I

In SQL
Server, the CASE functions evaluate a list of conditions and then return one or
many results. In this article, I am going to illustrate the various uses of
CASE functions in SQL server, in different places.

Method
1:
Usage of
simple case function

This is
the common use of a case function, where you can generate scalar values based
on a list of conditions.

Assume we
have the following table with id, [First name], [Last name] and gender as
columns and we want to create pre-nominal social titles on the fly, based on
the gender.


use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N’[emp]’)
and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [emp]
GO
create table Emp (id int, [First name] varchar(50),
[Last name] varchar(50), gender char(1))
go
insert into Emp (id,[First name],[Last name], gender )
values (1,’John’,’Smith’,’m’)
insert into Emp (id,[First name],[Last name], gender )
values (2,’James’,’Bond’,’m’)
insert into Emp (id,[First name],[Last name], gender )
values (3,’Alexa’,’Mantena’,’f’)
insert into Emp (id,[First name],[Last name], gender )
values (4,’Shui’,’Qui’,’f’)
insert into Emp (id,[First name],[Last name], gender )
values (5,’William’,’Hsu’,’m’)
insert into Emp (id,[First name],[Last name], gender )
values (6,’Danielle’,’Stewart’,’F’)
insert into Emp (id,[First name],[Last name], gender )
values (7,’Martha’,’Mcgrath’,’F’)
insert into Emp (id,[First name],[Last name], gender )
values (8,’Henry’,’Fayol’,’m’)
insert into Emp (id,[First name],[Last name], gender )
values (9,’Dick’,’Watson’,’m’)
insert into Emp (id,[First name],[Last name], gender )
values (10,’Helen’,’Foster’,’F’)
go

Now, let
us create a column [Full name] on the fly that calculates  pre-nominal
social titles like "Mr." and "Ms." based on the values from
the column Gender.


Select [id],[Full Name] = case Gender
when ‘m’ then ‘Mr. ‘+[First name]+ ‘ ‘+[Last name]
when ‘f’ then ‘Ms. ‘+[First name]+ ‘ ‘+[Last name]
end
from Emp

This
would produce the following result as shown below.


id Full Name
———– —————-
1 Mr. John Smith
2 Mr. James Bond
3 Ms. Alexa Mantena
4 Ms. Shui Qui
5 Mr. William Hsu
6 Ms. Danielle Stewart
7 Ms. Martha Mcgrath
8 Mr. Henry Fayol
9 Mr. Dick Watson
10 Ms. Helen Foster

Method
2:
Usage of a simple
case function with ELSE clause

If we add
another row with NULL as gender, we would not see the name at all in the result
set.

Insert
the following row onto the table emp.


use tempdb
go
insert into Emp (id,[First name],[Last name], gender ) values (11,’Bill’,’Gates’,NULL)
go

Now, let
us create a column [Full name] on the fly that calculates  pre-nominal
social titles like "Mr." and "Ms." based on the values from
the column Gender.


Select [id],[Full Name] = case Gender
when ‘m’ then ‘Mr. ‘+[First name]+ ‘ ‘+[Last name]
when ‘f’ then ‘Ms. ‘+[First name]+ ‘ ‘+[Last name]
end
from Emp

This
would produce the following result as shown below.

id          Full Name
———– ————————
1 Mr. John Smith
2 Mr. James Bond
3 Ms. Alexa Mantena
4 Ms. Shui Qui
5 Mr. William Hsu
6 Ms. Danielle Stewart
7 Ms. Martha Mcgrath
8 Mr. Henry Fayol
9 Mr. Dick Watson
10 Ms. Helen Foster
11 NULL

However, we need to display the Full Name irrespective of the
availability of the Gender values.

In order to achieve this we could use CASE with ELSE
function. Execute the following query as shown below.


Select [id],[Full Name] = case Gender
when ‘m’ then ‘Mr. ‘+[First name]+ ‘ ‘+[Last name]
when ‘f’ then ‘Mz. ‘+[First name]+ ‘ ‘+[Last name]
else [First name]+ ‘ ‘+[Last name]
end
from Emp

This
would produce the following result, as shown below.


id Full Name
———– ———————-
1 Mr. John Smith
2 Mr. James Bond
3 Mz. Alexa Mantena
4 Mz. Shui Qui
5 Mr. William Hsu
6 Mz. Danielle Stewart
7 Mz. Martha Mcgrath
8 Mr. Henry Fayol
9 Mr. Dick Watson
10 Mz. Helen Foster
11 Bill Gates

Method
3:
Usage of CASE functions
when there are two or more conditions in the list.

In the two
examples above, we saw the conditions were either Male, Female or None.
However, there are occasions when you have to use multiple conditions using
operators to return one value.

Let us
add a column [Marital Status] to this table and update the values as shown
below.


use tempdb
go
alter table Emp add [Marital Status] char(1) — S-Single M-Married
go
Update Emp set [Marital Status]=’S’ where id in (1,5,8)
Update Emp set [Marital Status]=’M’ where [Marital Status] is NULL
Go

Let us assume
that we want to display the persons name with a prefix that would help us
easily identify whether someone is married or single. Execute the following
query as shown below.


Select [id],[Full Name] = case
when Gender =’m’ and [marital status] =’S’ then ‘MR. ‘+[First name]+ ‘ ‘+[Last name]
when Gender =’m’ and [marital status] =’M’ then ‘Mr. ‘+[First name]+ ‘ ‘+[Last name]
when Gender =’f’ and [marital status] =’S’ then ‘Ms. ‘+[First name]+ ‘ ‘+[Last name]
when Gender =’f’ and [marital status] =’M’ then ‘Mrs. ‘+[First name]+ ‘ ‘+[Last name]
else [First name]+ ‘ ‘+[Last name]
end
from Emp

This
would produce the result shown below.


id Full Name
———– ——————–
1 MR. John Smith
2 Mr. James Bond
3 Mrs. Alexa Mantena
4 Mrs. Shui Qui
5 MR. William Hsu
6 Mrs. Danielle Stewart
7 Ms. Martha Mcgrath
8 MR. Henry Fayol
9 Mr. Dick Watson
10 Mrs. Helen Foster
11 Bill Gates

Conclusion

In this
article, we have seen an illustration of simple CASE functions in SQL Queries.
In Part 2, we will discuss how to use CASE functions in complicated situations.

»


See All Articles by Columnist
MAK

Latest Articles