w3resource

SQL Exercises: Where clause with like operator and escape character

SQL Wildcard & Special Operator: Exercise-14 with Solution

From the following table, write a SQL query to find rows in which col1 contains 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:
-- - Contains the sequence of characters '%//%'.
-- The ESCAPE clause is used to escape the special character '/' in the pattern.
WHERE col1 LIKE '%//%' ESCAPE '/';

Output of the Query:

col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/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 selects all columns (*) from a table called 'testtable' where the value in the "col1" column contains the characters "//" (with any characters before and after it).
The "ESCAPE '/'" clause specifies that when a forward slash character (/) is inserted into a string, it should be treated as an escape character, meaning that it should be treated as if it were a literal character (in this case, the second forward slash in the "LIKE" clause).

Explanation :

Syntax of using where clause with like operator and escape character

Visual presentation :

Exercise: Using where clause with like operator and escape character

Practice Online


Query Visualization:

Duration:

Query visualization of Using where clause with like operator and escape character - Duration

Rows:

Query visualization of Using where clause with like operator and escape character - Rows

Cost:

Query visualization of Using where clause with like operator and escape character - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Using where clause with not like, underscore operators.
Next SQL Exercise: Where clause with not like operator, 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.