SQL Exercises: Using where clause with not like, underscore operators

Last update on February 10 2025 07:55:00 (UTC/GMT +8 hours)

From the following table, write a SQL query to identify those rows where col1 does not contain the escape character underscore ( _ ). 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 any sequence of characters, followed by a forward slash '/'.
-- - Is not followed by an underscore '_'.
-- - Is not followed by any 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%45\2015/200
A001/DJ-402\44

Code Explanation:

The said SQL query selects all columns (*) from the 'testtable' table where the value of the "col1" column doesn't contain the character '/' preceded by an arbitrary character and succeeded by an arbitrary character.
The '%' is a wildcard character in SQL, which can match any string of any length (including an empty string) and the '_' is a single character wildcard. The 'LIKE' operator is used to search for a specific pattern in a column.
The 'ESCAPE' clause is used to search for the actual '/' character, which is treated as an escape character. The 'NOT' operator is used to exclude the rows that match the pattern defined in the 'LIKE' clause.

Explanation :

Visual presentation :

Practice Online

For more Practice: Solve these Related Problems:

• Write a SQL query to find rows where col1 does not contain any special characters. Return col1.
• Write a SQL query to retrieve rows where col1 starts with a letter and does not contain an underscore ( _ ). Return col1.
• Write a SQL query to find rows where col1 ends with a number and does not contain an underscore ( _ ). Return col1.
• Write a SQL query to list rows where col1 contains only alphanumeric characters and no underscores ( _ ). Return col1.

Contribute your code and comments through Disqus.

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