4 Examples of CASE expression in SQL
What is CASE and where is it used?
When a condition is met, the CASE statement evaluates different conditions and returns a scalar value. The phrase from the ELSE block will be returned if none of the conditions are evaluated to TRUE. The ELSE block, however, is optional. Above all, the CASE statement has two formats Simple CASE & Searched CASE.
The Searched CASE statement is highlighted in this post. To get the results, you'll need to use search and pattern matching.
4 CASE statement usages ane Examples
Now let's see different types of CASE statement and their example to understand this key SQL concept in detail.
1. Simple CASE statement
We
may use a simple CASE expression to compare various expressions instead
of creating stacked IF statements. A basic CASE expression compares the
first expression to the WHEN clause expression in this way.
If there is
a match, the expression following THEN is evaluated; otherwise, the
expression in the ELSE block is evaluated. It's worth noting that the
ELSE block is optional.
A simple CASE statement has the below-given syntax :
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
Now, let's work on a real-time example. Let's create a table named Customers with the following sample data as shown below :
Now,
let's write a CASE statement and demonstrate how and why it is used.
let's write a CASE statement by filtering countries. Let's do it for 2
countries Germany, Mexico, and the rest. The Query would be like
something below :
SELECT CustomerID, CustomerName, City,
CASE
WHEN Country = 'Germany' THEN 'German it is sire!'
WHEN Country = 'Mexico' THEN 'The country of Pablo!'
ELSE 'None other matters'
END AS CountryDescription
FROM Customers;
The result of the above query would be something like this :
2. searched CASE
CASE
was searched, and SIMPLE was expanded. This analyses a collection of
boolean expressions, such as pattern matching and range comparisons. If
any of the boolean expressions is TRUE, then the expression after THEN
is evaluated. If none of the boolean expressions after WHEN evaluate to
TRUE, the ELSE block is evaluated.
Economy
class is defined as a wage that is higher than 500 but less than 2000.
Furthermore, a salary of more than $2,000 is classified as a premium. If
the salary falls outside of a set range, an ELSE block with no travel is
provided.
The query for the same will be :
SELECT ID, Name, Gender, Salary,
CASE WHEN Salary >= 500 AND Salary < 2000 THEN 'Economy Class'
WHEN Salary >= 2000 THEN 'Premium Class'
ELSE 'No Travel'
END TravelMode
FROM
(VALUES (1,'Vish', 'M', 100)
,(2,'Atul', 'M', 200)
,(3,'Vishal','M', 500)
,(4,'Kasturi','F',2000)
,(5,'Belinda','F',5000))
as Emp(Id, Name, Gender, Salary);
The resultant would look like below :
ID Name Gender Salary TravelMode
----------- ------- ------ ----------- -------------
1 Vish M 100 No Travel
2 Atul M 200 No Travel
3 Vishal M 500 Economy Class
4 Kasturi F 2000 Premium Class
5 Belinda F 5000 Premium Class
3. Searched CASE with IN, OR and, LIKE operator
This example shows how to utilise CASE expressions with various clauses and operators. The IN, OR, and LIKE operators are used for pattern matching and comparison in the query below. In this situation, the IN and OR operators are practically identical. LIKE allows us to use pattern matching with ease.
The query would look like this :
SELECT ID, Name, Gender, Salary,
CASE WHEN Name IN ('Atul', 'Belinda') THEN 'Class 1'
WHEN Name LIKE 'K%' THEN 'Class 2'
WHEN (NAME = 'Vish' OR Name = 'Vishal') THEN 'Class 3'
ELSE 'No Class'
END as ClassInfo
FROM
(VALUES (1,'Vish', 'M', 100)
,(2,'Atul', 'M', 200)
,(3,'Vishal','M', 500)
,(4,'Kasturi','F',2000)
,(5,'Belinda','F',5000)
,(5,'Simona','M',5000))
as Emp(Id, Name, Gender, Salary);
The answer we get from the above query would look like below :
4. Searched CASE Expression with CHARINDEX & PATINDEX
Similarly, with the searched CASE, use the CHARINDEX and PATINDEX functions. The following example shows how to use the CHARINDEX function to determine if a character occurs in any given name. Pattern matching with PATINDEX to see if (Underscore) appears anywhere in the name column.
The query would look like this :
SELECT ID, Name, Gender, Salary,
CASE WHEN CHARINDEX('s', Name) > 0 THEN 'Contains char s'
WHEN PATINDEX('%_%',Name) > 0 THen 'Underscore Exists'
ELSE 'Optional'
END as ClassInfo
FROM
(VALUES (1,'Vish', 'M', 100)
,(2,'Atul_', 'M', 200)
,(3,'Vishal','M', 500)
,(4,'Kasturi','F',2000)
,(5,'Belinda_','F',5000)
,(5,'Simona','M',5000))
as Emp(Id, Name, Gender, Salary);
The answer we get from the above query would look like below :
ID Name Gender Salary ClassInfo
----------- -------- ------ ----------- -----------------
1 Vish M 100 Contains char s
2 Atul_ M 200 Underscore Exists
3 Vishal M 500 Contains char s
4 Kasturi F 2000 Contains char s
5 Belinda_ F 5000 Underscore Exists
5 Simona M 5000 Contains char s
Conclusion
That's all about the CASE expression in SQL. In this tutorial, you have learned about the Searched CASE expression in this post. The two most popular forms are simple and searched cases. In comparison to Simple, we can accomplish more with Searched case thanks to extended pattern machining and the usage of functions. Furthermore, the majority of the functions interact with Searched CASE in order to do more.
My Other SQL Articles and Tutorials you may like
* Difference between DDL and DML commands in SQL
* How to remove duplicate rows in SQL
* How to concatenate columns in SQL
* How to find Nth Highest salary in SQL
* How to use Cross Join in SQL
* How to use ORDER BY in SQL with example
* How to use row_number function in SQL
* 15 SQL Interview Questions with Answers
* How to use LEFT, RIGHT, and SELF JOIN
* What is a Virtual column in MySQL? Example
* How to do pagination in SQL with Examples
* How to transpose data in SQL with examples
Thanks
for reading this article so far. If you like these common examples of CASE expression in SQL then please share it with
your friends and colleagues. If you have any questions or feedback then
please drop a note.
