T-SQL Programming Part 5 – Using the CASE Function
Have you ever wanted to replace a column value with a different value based on the original column value? Learn how, with the T-SQL 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:
And the “Searched Format” looks like this:
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:
Here is the output from this T-SQL code:
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”:
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.
The output for the above command on my server displays the following:
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.”
The output on my machine for this query looks like this:
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.
The output for this query looks like this:
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.
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