Think of this as the IF...THEN statement for SQL. Most languages have a way to determine if some condition has occurred then return this value. The CASE function is the way to do that in SQL.
The CASE function is used to vary a result expression based upon one or more conditions. ELSE is also a part of the CASE condition and is used as the logical NOT of the selected case.
CASE has two formats for working with data:
Evaluation of boolean expressions to determine the result.
CASE should not be used in a WHERE clause
See the complete article on how to rewrite queries that have a case in a where clause.
Example Schema
Column Name | Column Type |
---|---|
AppType | Char(1) |
Name | VarChar(64) |
AppCost | Int |
Example Data
AppType | Name | AppCost |
---|---|---|
A | SomeApp.exe | 40 |
S | WatchThis.scr | 125 |
Z | Setup.zip | 200 |
D | VistaDB.DLL | 750 |
Q | Unknown.pdb | 0 |
The SQL statement will replace the single letter 'A' with 'Application' in the result set.
Exact Matching Example |
Copy Code
|
---|---|
SELECT Name, CASE AppType WHEN 'A' THEN 'Application' WHEN 'S' THEN 'Screen Saver' WHEN 'D' THEN 'DLL' WHEN 'Z' THEN 'ZIP' ELSE 'Unknown Type' END FROM AppTable |
This SQL example will return a string from the AppCost rather than the exact number.
Evaluated CASE Expressions |
Copy Code
|
---|---|
SELECT Name, CASE WHEN AppCost = 0 THEN 'Free' WHEN AppCost < 50 THEN 'Under $50' WHEN AppCost >= 50 and AppCost < 250 THEN 'Under $250' WHEN AppCost >= 250 and AppCost < 1000 THEN 'Under $1000' ELSE 'Special Pricing' END AS PRICETYPECOL FROM AppTable |