Online College Courses for Credit

+
1 Tutorials that teach LIKE to Search Data
Take your pick:
LIKE to Search Data

LIKE to Search Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a SELECT statement that uses LIKE to search for a specific pattern of text in a column.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the LIKE operator within the WHERE clause of a SELECT statement to search specific patterns of text in 3 parts:

  1. Getting Started
  2. The % Operator
  3. The _ Operator

1. Getting Started

There are special operators that allow us to check if a value is in a range. The LIKE operator in a WHERE clause allows you to check if an attribute’s value matches a specific string pattern using wildcards. There are two different wildcards that can be used with the LIKE operator.

  • The percent sign (%) represents zero, one, or multiple characters or numbers.
  • The underscore (_) represents a single character or number.
These symbols can be used in combination with one another along with specific characters or numbers. If neither one of these signs is used with a LIKE clause, the LIKE simply would work in the same way as an equal (=) operator. If just a (%) is used in the LIKE clause, it would return all rows of data.

It is important to note that the string text is case sensitive with the content that is not in the wildcards so the matches will only display if the query entry is the same as what is in the table data.

If we have a string ‘abc’ in a table, the following comparisons will return the data if it were compared against:

  • ‘abc’ would return true as the string matches exactly.
  • ‘a%’ would return true as it looks for the letter ‘a’ and 0 or more characters displayed afterward.
  • ‘_b_’ would return true as it looks for one character, then the letter b and the one more character.
  • ‘c’ would return false as it is only looking at the letter c.
  • ‘_b’ would return false as it looks for one character and then the letter b.
  • ‘%c’ would return true as it looks for any number of characters and then the letter c at the end.

2. The % operator

As we showed above, the % searches for zero, one, or more characters as a replacement. It will be the most common operator used in the LIKE clause. If we wanted to query the customer table and list all of the customers whose name starts with the letter L, we would use the % wildcard after the letter L:

SELECT *
FROM customer
WHERE first_name like 'L%'

File:11201-1140-1.png

If we wanted to list all of the customers who have their email in the domain gmail.com, we would have the % wildcard operator before @gmail.com:

SELECT *
FROM customer
WHERE email like '%@gmail.com'

File:11202-1140-2.png

This will be the most common way to use the % operator to search for content. In the following tutorial, we’ll dive into more complex ways to work with wildcards.


3. The _ Operator

The underscore (_) operator may seem like a simpler version of the % operator but it matches a single wildcard character, unlike the % that has 0 or more. This allows us to query data to look for a specific length of data. If we wanted to look for customers that have the state starting with C and having two characters, we can do:

SELECT *
FROM customer
WHERE state LIKE 'C_'


File:11203-1140-3.png

Another example may be looking for those that live in a country that has 3 characters and starts with the letter U. If we used the % operator, it would search for 0 or more characters after U:

SELECT *
FROM customer
WHERE country LIKE 'U%'

This would return some extra rows of countries like the United Kingdom:


File:11204-1140-4.png

If we changed it to use the two extra underscores to get 3 characters, it should look like:

SELECT *
FROM customer
WHERE country LIKE 'U__'

File:11205-1140-5.png

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next try your own choices for which columns you want the query to provide.

summary
The LIKE clause allows us to match text strings using the wildcards using the % and _ characters. You can use them in combination with one another to improve searches for specific content.