Case Statement
The CASE statement goes through conditions and returns a value when the first condition is TRUE (similar to IF-THEN-ELSE statement).
Case statement can be used in select queries along with Where, Order By and Group By clause. It can also be used in the Insert statement.
SQL CASE Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
Example:
Lets use the “Products” table to shorten the name of the “SupplierName” for suppliers named “TechOne” and “TechThree”.
| ProductID | ProductName | Description | Price | SupplierName | SupplierID |
|---|---|---|---|---|---|
| 1 | Super Printer | 9000 series printer | 300 | TechOne | 1 |
| 2 | Laptop 5 | 4000 Laptop 5th generation | 700 | TechTwo | 2 |
| 3 | 3D Printer | 3D Print Pro | 900 | TechOne | 1 |
| 4 | Labtop 7 | 100 Labtop 7 processor | 1200 | TechTwo | 2 |
| 5 | Camera 400 | Camera Ultra 400 | 400 | TechThree | 3 |
SELECT
CASE SupplierName
When 'TechOne' Then 'T1'
When 'TechThree' Then 'T3'
Else SupplierName
End as ShortName,
SupplierID
From Products
Results:
TechOne and TechThree are changed to a shorter name and TechTwo retains its original format.
| ShortName | SupplierID |
|---|---|
| T1 | 1 |
| TechTwo | 2 |
| T1 | 1 |
| TechTwo | 2 |
| T3 | 3 |
SQL Tutorial