SQL with Examples : SQL LIKE Operator

Hits: 7

SQL LIKE Operator

Until now you have been using operators such as conditional operators (for testing equality), BETWEEN operator (for checking against a range of values) or IN operator (for checking against a set of values) in which one thing is common; They all search for an exact match.

Filtering data that way is all well and good. But sometimes a situation can arise when you want to search for partial string matches, for example, you want to search for all products that include a text ‘Chocolate Cookie’ within their name. You cannot achieve this with simple comparison operators; This is where you need a wildcard search.

Wildcards are special characters with which you can create search patterns that can be compared against your data.

The LIKE Operator

To use wildcards in search clauses, the LIKE operator is used. LIKE instructs the DBMS to search using a wildcard match rather than a straight equality match.

The LIKE operator has the following syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE search_pattern;

The search_pattern is a sequence of characters to search for in the column. It is made up of literal text, wildcard characters, or a combination of both.

The Following wildcard characters are used to create a search pattern:

Wildcard character Description
% The percent sign matches any string of zero or more characters.
_ The underscore matches a single character.

Let’s take a look at them one by one.

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.

This table is part of an ‘Product Management System’ used by an imaginary distributor of cookies.

ID Name Price ($)
1 Vanilla Cookies 3.00
2 Cashew Nut Cookies 3.00
3 Chocolate Cookies 4.00
4 Chocolate Orange Cookies 5.00
5 White Chocolate Cookies 6.00
6 Dark Chocolate Chip Cookies 6.00
7 Double Chocolate Chip Cookies 7.00
8 4 Ct. Cookies Variety Pack 6.00
9 8 Ct. Cookies Variety Pack 9.00
10 12 Ct. Cookies Variety Pack 12.00

The Percent Sign % Wildcard

The percent sign % is the most commonly used wildcard. % Matches zero, one or more characters at a specified location within a search string. For example, if you wanted to search for all products starting with the word ‘Chocolate’, you could write a query like:

SELECT *
FROM Products
WHERE Name LIKE 'Chocolate%';
ID Name Price ($)
3 Chocolate Cookies 4.00
4 Chocolate Orange Cookies 5.00

In this example the search pattern Chocolate% tells the DBMS to match any value starting with the text ‘Chocolate’, regardless of any character after it.

You can use several wildcards to create a search pattern. The following example uses two wildcards, one at either end:

SELECT *
FROM Products
WHERE Name LIKE '%Chocolate%';
ID Name Price ($)
3 Chocolate Cookies 4.00
4 Chocolate Orange Cookies 5.00
5 White Chocolate Cookies 6.00
6 Dark Chocolate Chip Cookies 6.00
7 Double Chocolate Chip Cookies 7.00

The above example uses the search pattern %Chocolate% which matches any value that contains the text ‘Chocolate’ anywhere within it, regardless of any characters before or after that text. It is important to note that % also matches zero characters; that’s why the result-set also includes products whose name starts with ‘Chocolate’.

Wildcards can be used anywhere within the search pattern, including in the middle, although this is rarely useful. The following example finds all products that start with a ‘D’ and end with ‘Cookies’.

SELECT *
FROM Products
WHERE Name LIKE 'D%Cookies';
ID Name Price ($)
6 Dark Chocolate Chip Cookies 6.00
7 Double Chocolate Chip Cookies 7.00

The Underscore _ Wildcard

Like the percent sign %, the underscore _ is another wildcard character that is used extensively. The underscore is used the same way as the %, but the underscore matches only one character, rather than matching multiple characters.

Below example specifies the search pattern containing a single underscore wildcard followed by literal text.

SELECT *
FROM Products
WHERE Name LIKE '_ Ct. Cookies Variety Pack';
ID Name Price ($)
8 4 Ct. Cookies Variety Pack 6.00
9 8 Ct. Cookies Variety Pack 9.00

As you can see only two rows are retrieved as the underscore matches 4 in the first row and 8 in the second row. But the ’12 Ct. Cookies Variety Pack’ did not match because the search pattern required only one wildcard match, not two.

More Examples

Below table shows some more search expressions and their interpretation.

Search expression Interpretation
B% Finds any values that start with ‘B’
%t Finds any values that ends with ‘t’
%bas% Finds any values that have ‘bas’ in any position
_ir% Finds any values that have ‘ir’ in the second and third positions
A__% Finds any values that start with ‘A’ and are at least three characters in length
%0 Finds any values that end with ‘0’
_c%1 Finds any values that have a ‘c’ in the second position and end with a ‘1’
2___3 Finds any values in a five-digit number that start with ‘2’ and end with ‘3’
a%o Finds any values that start with ‘a’ and ends with ‘o’
__t_ Finds any four-character string with a ‘t’ in the third position
___-__-____ Finds any 11-character string with dashes in the fourth and seventh positions

The NOT LIKE Operator

The LIKE operator is negated as NOT LIKE. When NOT LIKE is used, only values that are not similar are returned. For example, if you wanted to search for all products that do not have the word ‘chocolate’ anywhere in their name, you could write a query like:

SELECT *
FROM Products
WHERE Name NOT LIKE '%Chocolate%';
ID Name Price ($)
1 Vanilla Cookies 3.00
2 Cashew Nut Cookies 3.00
8 4 Ct. Cookies Variety Pack 6.00
9 8 Ct. Cookies Variety Pack 9.00
10 12 Ct. Cookies Variety Pack 12.00

 

 

Python Example for Beginners

Two Machine Learning Fields

There are two sides to machine learning:

  • Practical Machine Learning:This is about querying databases, cleaning data, writing scripts to transform data and gluing algorithm and libraries together and writing custom code to squeeze reliable answers from data to satisfy difficult and ill defined questions. It’s the mess of reality.
  • Theoretical Machine Learning: This is about math and abstraction and idealized scenarios and limits and beauty and informing what is possible. It is a whole lot neater and cleaner and removed from the mess of reality.

Data Science Resources: Data Science Recipes and Applied Machine Learning Recipes

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Recipes in Project-Based Learning:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

Comparing Different Machine Learning Algorithms in Python for Classification (FREE)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.