T-SQL Programming Part 5 - Using the CASE Function

January 7, 2004

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers