w3resource

SQL Exercises: Using like operator and escape character

SQL Wildcard & Special Operator: Exercise-16 with Solution

From the following table, write a SQL query to find those rows where col1 contains the string ( _/ ). 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 '%/_//%' where the underscore '_' is followed by a forward slash '/'.
-- 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

Code Explanation:

The said SQL query is selecting all columns (*) from a table called 'testtable' where the value in the column "col1" contains a string that starts and ends with '%' and has the string "/_//" in between. The ESCAPE '/' clause is used to indicate that the '/' character is being used as an escape character for the LIKE clause, which means that any '/' characters that appear in the string being searched for should be treated as literal characters, rather than as special characters.

Explanation :

Syntax of using like operator and escape character

Visual presentation :

Exercise: Using like operator and escape character

Practice Online


Query Visualization:

Duration:

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

Rows:

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

Cost:

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

Contribute your code and comments through Disqus.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/sql-wildcard-special-operator-exercise-16.php