SQL Exercises: Filter rows using not like and % character

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

From the following table, write a SQL query to find those rows where col1 does not contain 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:
-- - Does not contain 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 NOT 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\44

Code Explanation:

The said SQL query that is selecting all columns (*) from a table called 'testtable' and filtering the results based on a condition involving the "col1" column.
The condition is that the values in "col1" should not match a specific pattern using the "LIKE" operator and using '/' as escape character. The pattern being matched against is "%/%%", which is essentially saying "any string that contains the character '/' followed by any number of characters". The "NOT" keyword is used to invert the condition, so the query will return all rows where "col1" does not match this pattern.

Explanation :