Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 22, 2007

CASE function in SQL Server 2005 - part I

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM