CASE function in SQL Server 2005 - part I

March 22, 2007

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