Sep
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.
