SQL Exercises: Using where clause like, underscore, escape operators

Last update on December 20 2024 11:56:14 (UTC/GMT +8 hours)

From the following table, write a SQL query to find those rows where col1 contains the escape character underscore ( _ ). Return col1.

Sample table: testtable

col1
--------------------------
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ-402%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300
A001/DJ-402\44

Sample Solution:

-- This query selects all columns from the 'testtable'.
SELECT *
-- Specifies the table from which to retrieve the data (in this case, 'testtable').
FROM testtable
-- Filters the rows to only include those where the 'col1' column:
-- - Contains any sequence of characters, followed by a forward slash '/'.
-- - Is followed by an underscore '_'.
-- - Is followed by any sequence of characters.
-- The ESCAPE clause is used to escape the special character '/' in the pattern.
WHERE col1 LIKE '%/_%' ESCAPE '/';

Output of the Query:

col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300

Code Explanation

The given SQL query selects all columns (*) from the 'testtable' table where the value of the "col1" column contains the character '/' preceded by an arbitrary character and succeeded by an arbitrary character.
It is important to note that '%' is used in SQL as a wildcard character, which can match any string of any length (even an empty string) and '_' is used as a single character wildcard.
The 'LIKE' operator is used to search for a specific pattern in a column.
The 'ESCAPE' clause is used to search for the actual '/' character, which is treated as an escape character.

Explanation :