w3resource

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

SQL Wildcard & Special Operator: Exercise-13 with Solution

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 :

Syntax of using where clause with not like, underscore operators

Visual presentation :

Exercise: SQL using where clause with not like, underscore operators

Practice Online


Query Visualization:

Duration:

Query visualization of Using where clause with not like, underscore operators - Duration

Rows:

Query visualization of Using where clause with not like, underscore operators - Rows

Cost:

Query visualization of Using where clause with not like, underscore operators - Cost

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.

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.