SQL Exercises: Where clause with not like operator, escape character

SQL Wildcard & Special Operator: Exercise-15 with Solution

From the following table, write a SQL query to identify those rows where col1 does not contain 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:
-- - Does not contain the sequence of characters '%//%'.
-- 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

Code Explanation

The said SQL query selects all columns (*) from a table called "testtable" where the value in the "col1" column does not contain the characters "//" (with any characters before and after it).
There is a clause in the code, the "ESCAPE '/'", that specifies that the forward slash characters (/) are used as escape characters, meaning that they signal the following characters need to be treated as literals (in this case the second forward slash in the "NOT LIKE" clause).

Explanation :

Visual presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Where clause with like operator and escape character.
Next SQL Exercise: Using like operator and escape character.