SQL Exercises: Find rows using like operator and % character

SQL Wildcard & Special Operator: Exercise-18 with Solution

From the following table, write a SQL query to find those rows where col1 contains the character percent ( % ). 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 '%/%%' where the percent '%' is followed by another percent.
-- 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%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300

Code Explanation:

The said SQL query that is selecting all columns (*) from a table called 'testtable' and filtering the results based on the condition that the value in column "col1" matches a specific pattern, using the "LIKE" operator.
The pattern being searched for is specified using wildcard characters ('%') and the escape character '/' is used to escape any instances of the '/' character within the pattern. The result will be all rows from the testtable where the col1 match the pattern specified in the like statement, in this case it will select all the rows which contain '%' after '/' in col1.

Explanation :

Visual presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Using not like operator and escape characters.
Next SQL Exercise: Filter rows using not like and % character.