w3resource

SQL Exercises: Filter rows using not like and % character


19. Rows Without Percent Character

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 :

Syntax of filter rows using not like and % character

Visual presentation :

Exercise: Filter rows using not like and % character

Go to:


PREV : Rows with Percent Character.
NEXT : Customers Without Grade.


Practice Online



For more Practice: Solve these Related Problems:

  • Write a SQL query to find rows where col1 contains only letters and numbers but no percent characters ( % ). Return col1.
  • Write a SQL query to retrieve rows where col1 starts with a letter and does not contain a percent character ( % ). Return col1.
  • Write a SQL query to find rows where col1 ends with a number and does not contain a percent character ( % ). Return col1.
  • Write a SQL query to list rows where col1 contains only alphanumeric characters and no percent characters ( % ). Return col1.


Contribute your code and comments through Disqus.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.