SQL Exercises: Using not like operator and escape characters

Last update on September 22 2025 12:46:43 (UTC/GMT +8 hours)

17. Rows Without '_/' String

From the following table, write a SQL query to find those rows where col1 does not contain the string ( _/ ). 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 underscore '_' is followed by a forward slash '/'.
-- 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-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

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" does not match a specific pattern, using the "NOT LIKE" operator. The pattern being searched for is specified using wildcard characters ('%' and '_') 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 does not match the pattern specified in the like statement.

Explanation:

Visual presentation :

Go to:

• SQL Wildcard and Special operators Exercises Home ↩
• SQL Exercises Home ↩

PREV : Rows with '_/' String.
NEXT : Rows with Percent Character.

Practice Online

For more Practice: Solve these Related Problems:

• Write a SQL query to find rows where col1 contains an underscore ( _ ) but not followed by a forward slash ( / ). Return col1.
• Write a SQL query to retrieve rows where col1 contains a forward slash ( / ) but not preceded by an underscore ( _ ). Return col1.
• Write a SQL query to find rows where col1 contains neither '_/' nor '/_'. Return col1.
• Write a SQL query to list rows where col1 contains only alphanumeric characters and no '_/' or '/_'. Return col1.

Contribute your code and comments through Disqus.