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 :
Visual presentation :
Practice Online
Query Visualization:
Duration:
Rows:
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.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics