SQL Exercises: Where clause with like operator and escape character

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

From the following table, write a SQL query to find rows in which col1 contains the forward slash character ( / ). 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 the 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_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

Code Explanation:

The said SQL query selects all columns (*) from a table called 'testtable' where the value in the "col1" column contains the characters "//" (with any characters before and after it).
The "ESCAPE '/'" clause specifies that when a forward slash character (/) is inserted into a string, it should be treated as an escape character, meaning that it should be treated as if it were a literal character (in this case, the second forward slash in the "LIKE" clause).

Explanation :

Visual presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Using where clause with not like, underscore operators.
Next SQL Exercise: Where clause with not like operator, escape character.