CASEexpression1WHENexpression2THENexpression3, ...
[ ELSEexpression4 ]
END
SELECT id,
( CASE name
WHEN 'Tee Shirt' THEN 'Shirt'
WHEN 'Sweatshirt' THEN 'Shirt'
WHEN 'Baseball Cap' THEN 'Hat'
ELSE 'Unknown'
END ) as Type
FROM Product;
For compatibility reasons, you can end this expression with either ENDCASE or END CASE.
You can use case expressions anywhere you can use regular expression.
Syntax 1
If the expression following the CASE keyword is equal to the expression following the first WHEN keyword, then the
expression following the associated THEN keyword is returned. Otherwise the expression following the ELSE keyword is returned,
if specified.
For example, the following code uses a case expression as the second clause in a SELECT statement. It selects a row from the
Product table where the name column has a value of Sweatshirt.
Syntax 2
If the search-condition following the first WHEN keyword is TRUE, the expression following the associate THEN keyword
is returned. Otherwise the expression following the ELSE clause is returned, if specified.
NULLIF function for abbreviated CASE expressions
The NULLIF function provides a way to write some CASE statements in short form. The syntax for NULLIF is as follows:
NULLIF ( expression-1, expression-2 )
NULLIF compares the values of the two expressions. If the first expression equals the second expression, NULLIF returns NULL.
If the first expression does not equal the second expression, NULLIF returns the first expression.
The following statement uses a CASE expression as the third clause of a SELECT statement to associate a string with a search
condition. If the name column's value is Tee Shirt, this query returns Sale. And if the name column's value is not Tee Shirt and the quantity is greater than fifty, it returns Big Sale. However, for all others, the query then returns Regular price.
SELECT id, name,
( CASE
WHEN name='Tee Shirt' THEN 'Sale'
WHEN quantity >= 50 THEN 'Big Sale'
ELSE 'Regular price'
END ) as Type
FROM Product;
Syntax 1