T-SQL Programming Part 5 – Using the CASE Function

The CASE function is a very
useful T-SQL function. With this function you can replace a column value with
a different value based on the original column value. An example of where this
function might come in handy is where you have a table that contains a column named
SexCode, where 0 stands for female, 1 for male, etc., and you want to return
the value “female” when the column value is 0, or “male” when the column value
is 1, etc.. This article will discuss using the CASE function in a T-SQL SELECT
statement.

The CASE function allows you
to evaluate a column value on a row against multiple criteria, where each criterion
might return a different value. The first criterion that evaluates to true
will be the value returned by the CASE function. Microsoft SQL Server Books
Online documents two different formats for the CASE function. The “Simple
Format” looks like this:


CASE input_expression
WHEN when_expression THEN result_expression
[ …n ]
[
ELSE else_result_expression
]
END

And the “Searched Format”
looks like this:


CASE
WHEN Boolean_expression THEN result_expression
[ …n ]
[
ELSE else_result_expression
]
END

Where the “input_expression”
is any valid Microsoft SQL Server expression, the “when_expression” is
the value in which the input_expression is compared, the “result_expression
is the value that will be return for the CASE statement if the “when_expression”
evaluates to true, “[…n]” represents that multiple WHEN conditions can exist,
the “else_result_expression” is the value that will be returned if no “when_expression”
evaluates to true and in the “Searched Format” the “Boolean_expression”
is any Boolean express that when it evaluates to true will return the “result_expression”.
Let me go through a couple of examples of each format to help you better
understand how to use the CASE function in a SELECT statement.

For the first example let me
show you how you would use the CASE function to display a description, instead
of a column value that contains a code. I am going to use my earlier example
that I described at the top of this article where I discussed displaying
“female” or “male” instead of the SexCode. Here is my example T-SQL Code:


create table patients (PatientID int identity,
PatientName varchar(50),
PatientSexCode int)
insert into patients(PatientName, PatientSexCode)
values (‘Doe, Jane’, 0)
insert into patients(PatientName, PatientSexCode)
values (‘Doe, Dick’, 1)
insert into patients(PatientName, PatientSexCode)
values (‘Doe, Spot’, 2)
insert into patients(PatientName, PatientSexCode)
values (‘xxxxxxxx’, 9)
select PatientName,
case PatientSexCode
when 0 then ‘female’
when 1 then ‘male’
when 2 then ‘unknown’
else ‘Invalid PatientSexCode’ end as “Patient Sex”
from patients

Here is the output from this
T-SQL code:


PatientName Patient Sex
————————— ———————–
Doe, Jane female
Doe, Dick male
Doe, Spot unknown
xxxxxxxx Invalid PatientSexCode

This example shows the
syntax in action for a CASE function using the “Simple Format”. As you can see
the CASE function evaluates the PatientSexCode to determine if it is a 0, 1, or
2. If it is a 0, then “female” is displayed in the output for the “Patient
Sex” column. If the PatientSexCode is 1, then “male” is display, or if
PatientSexCode is 2 then “unknown” is displayed. Now if the PatientSexCode is
anything other than a 0, 1 or 2 then the “ELSE” condition of the CASE function
will be used and “Invalid PatientSexCode” will be displayed for the “Patient
Sex” column.

Now the same logic could be
written using a “Searched Format” for the CASE function. Here is what the
SELECT statement would look like for the “Searched Format”:


select PatientName,
case
when PatientSexCode = 0 then ‘female’
when PatientSexCode = 1 then ‘male’
when PatientSexCode = 2 then ‘unknown’
else ‘Invalid PatientSexCode’ end as “Patient Sex”
from patients

Note the slight differences
between the “Simple” and “Searched” formats. In the “Simple” format I specified
the column name for which row values will be compared against the “when_expressions”
,where as in the “Searched” format each WHEN condition contains a Boolean
expression that compares the PatientSexCode column against a code value.

Now the CASE function can be
considerably more complex than the basic examples I have shown. Suppose you
want to display a value that is based on two different columns values in a
row. Here is an example that determines if a Product in the Northwind database
is of type Tins or Bottles, and is not a discontinued item.


Select top 8 ProductName,
case
when QuantityPerUnit like ‘%Tins%’ and
Discontinued = 0
then ‘Tins’
when QuantityPerUnit like ‘%bottles%’ and
Discontinued = 0
then ‘Bottles’
else ‘Not Tins, Not Bottles, or is Discontinued’
end ‘Type or Availability’
from Northwind.dbo.Products
order by ProductName

The output for the above
command on my server displays the following:


ProductName Type or Availability
———————– —————————————–
Alice Mutton Not Tins, Not Bottles, or is Discontinued
Aniseed Syrup Bottles
Boston Crab Meat Tins
Camembert Pierrot Not Tins, Not Bottles, or is Discontinued
Carnarvon Tigers Not Tins, Not Bottles, or is Discontinued
Chai Not Tins, Not Bottles, or is Discontinued
Chang Bottles
Chartreuse verte Not Tins, Not Bottles, or is Discontinued

As you can see I’m using a
“Searched Format” for this CASE function call. Also, each WHEN clause contains
two different conditions. One condition to determine the type (tins, or
bottles) and another condition to determine if the product has been
discontinued. If the QuantityPerUnit contains the string “Tins” and the
Discontinue column value is 0 then the “Type of Availability” is set to “Tins”.
If the QuantityPerUnit contains the string “Bottles” and the Discontinue column
value is 0 then the “Type of Availability” is set to “Bottles”. For all other conditions,
the “Type or Availability” is set to “Not Tins", "Not Bottles",
or is "Discontinued."

The WHEN clauses in the CASE
function are evaluated in order. The first WHEN clause that evaluates to
“True” determines the value that is returned from the CASE function. Basically,
multiple WHEN clauses evaluate to “True”, only the THEN value for the first WHEN
clause that evaluates to “True” is used as the return value for the CASE
function. Here is an example where multiple WHEN clauses are “True.”


select top 5 title,
case
when price < 12.00 then ‘Cheap’
when price < 3.00 then ‘Really Cheap’
when price > 12.00 and price < 20.00 then ‘Average’
else ‘Expensive’ end ‘Price Category’
from pubs.dbo.titles

The output on my machine for
this query looks like this:


title Price Category
——————————————————– ————
The Busy Executive’s Database Guide Average
Cooking with Computers: Surreptitious Balance Sheets Cheap
You Can Combat Computer Stress! Cheap
Straight Talk About Computers Average
Silicon Valley Gastronomic Treats Average

If you look at the raw
titles table data in the pubs database for the title “You Can Combat Computer
Stress!” you will note that the price for this book is $2.99. This price makes
both the “price < 12.00” and “price < 3.00” conditions “True”. Since the
conditions are evaluated one at a time, and the “price < 12.00” is evaluated
prior to the “price < 3.00,” the “Price Category” for the title “You Can
Combat Computer Stress!” is set to “Cheap”.

The CASE function can appear
in different places within the SELECT statement, it does not have to only be in
the selection list within the SELECT statement. Here is an example where the CASE
function is used in the WHERE clause.


select top 5 title, price
from pubs.dbo.titles
where
case
when price < 12.00 then ‘Cheap’
when price < 3.00 then ‘Really Cheap’
when price > 12.00 and price < 20.00 then ‘Average’
else ‘Expensive’ end = ‘Average’

The output for this query
looks like this:


title price
——————————————————– ————–
The Busy Executive’s Database Guide 19.9900
Straight Talk About Computers 19.9900
Silicon Valley Gastronomic Treats 19.9900
Prolonged Data Deprivation: Four Case Studies 19.9900
Sushi, Anyone? 14.9900

Here I only wanted to display
books from the titles table in pubs database if the price category is
‘Average’. By placing my CASE function in the WHERE clause I was able to
accomplish this.

Conclusion

As you can see the CASE
function is an extremely valuable function. It allows you to take a data
column and represent it differently depending on one or more conditions
identified in the CASE function call. I hope that the next time you need to
display or use different values for specific column data you will review the
CASE function to see if it might meet your needs.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles