SQL LIKE
Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.
In this lesson we'll cover:
The SQL LIKE operator
LIKE is a logical operator in SQL that allows you to match on similar values rather than exact ones.
In this example, the results from the Billboard Music Charts dataset will include rows for which "group" starts with "Snoop" and is followed by any number and selection of characters.
Run the code to see which results are returned.
Note: "group" appears in quotations above because GROUP is actually the name of a function in SQL. The double quotes (as opposed to single: ' ) are a way of indicating that you are referring to the column name "group" , not the SQL function. In general, putting double quotes around a word or phrase will indicate that you are referring to that column name.
Wildcards and ILIKE
The % used above represents any character or set of characters. In this case, % is referred to as a "wildcard." In the type of SQL that Mode uses, LIKE is case-sensitive, meaning that the above query will only capture matches that start with a capital "S" and lower-case "noop." To ignore case when you're matching values, you can use the ILIKE command:
You can also use _ (a single underscore) to substitute for an individual character:
Sharpen your SQL skills
Practice Problem
Write a query that returns all rows for which Ludacris was a member of the group.
Practice Problem
Write a query that returns all rows for which the first artist listed in the group has a name that begins with "DJ".
SQL: LIKE Condition
This SQL tutorial explains how to use the SQL LIKE condition (to perform pattern matching) with syntax, examples, and practice exercises.
Description
The SQL LIKE condition allows you to use wildcards to perform pattern matching in a query. The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the LIKE condition in SQL is:
Parameters or Arguments
A character expression that contains pattern matching. The wildcards that you can choose from are:
| Wildcard | Explanation |
|---|---|
| % | Allows you to match any string of any length (including zero length) |
| _ | Allows you to match on a single character |
ESCAPE ‘escape_character’ Optional. It allows you to pattern match on literal instances of a wildcard character such as % or _ .
DDL/DML for Examples
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!
Example — Using % Wildcard in the LIKE Condition
Let’s explain how the % wildcard works in the SQL LIKE condition. Remember that the % wildcard matches any string of any length (including zero length).
In this first example, we want to find all of the records in the customers table where the customer’s last_name begins with ‘J’.
In this example, we have a table called customers with the following data:
| customer_id | last_name | first_name | favorite_website |
|---|---|---|---|
| 4000 | Jackson | Joe | techonthenet.com |
| 5000 | Smith | Jane | digminecraft.com |
| 6000 | Ferguson | Samantha | bigactivities.com |
| 7000 | Reynolds | Allen | checkyourmath.com |
| 8000 | Anderson | Paige | NULL |
| 9000 | Johnson | Derek | techonthenet.com |
Enter the following SQL statement:
There will be 2 records selected. These are the results that you should see:
| customer_id | last_name | first_name | favorite_website |
|---|---|---|---|
| 4000 | Jackson | Joe | techonthenet.com |
| 9000 | Johnson | Derek | techonthenet.com |
This example returns the records in the customers table where the last_name starts with ‘J’. As you can see, the records for the last names Jackson and Johnson have been returned.
Because the LIKE condition is not case-sensitive, the following SQL statement would return the same results:
Using Multiple % Wildcards in the LIKE Condition
You can also using the % wildcard multiple times with the LIKE condition.
Using the same customers table with the following data:
| customer_id | last_name | first_name | favorite_website |
|---|---|---|---|
| 4000 | Jackson | Joe | techonthenet.com |
| 5000 | Smith | Jane | digminecraft.com |
| 6000 | Ferguson | Samantha | bigactivities.com |
| 7000 | Reynolds | Allen | checkyourmath.com |
| 8000 | Anderson | Paige | NULL |
| 9000 | Johnson | Derek | techonthenet.com |
Let’s try to find all last_name values from the customers table where the last_name contains the letter ‘e’. Enter the following SQL statement:
There will be 3 records selected. These are the results that you should see:
| last_name |
|---|
| Anderson |
| Ferguson |
| Reynolds |
In this example, the last names Anderson, Ferguson and Reynolds contain the letter ‘e’.
Example — Using _ Wildcard in the LIKE Condition
Next, let’s explain how the _ wildcard (underscore wildcard) works in the LIKE condition. Remember that _ wildcard is looking for exactly one character, unlike the % wildcard.
Using the categories table with the following data:
| category_id | category_name |
|---|---|
| 25 | Deli |
| 50 | Produce |
| 75 | Bakery |
| 100 | General Merchandise |
| 125 | Technology |
Let’s try to find all records from the categories table where the category_id is 2-digits long and ends with ‘5’. Enter the following SQL statement:
There will be 2 records selected. These are the results that you should see:
| category_id | category_name |
|---|---|
| 25 | Deli |
| 75 | Bakery |
In this example, there are 2 records that will pattern match — the category_id values 25 and 75. Notice that the category_id of 125 was not selected because, the _ wilcard matches only on a single character.
Using Multiple _ Wildcards in the LIKE Condition
If you wanted to match on a 3-digit value that ended with ‘5’, you would need to use the _ wildcard two times. You could modify your query as follows:
Now you will return the category_id value of 125:
| category_id | category_name |
|---|---|
| 125 | Technology |
Example — Using the NOT Operator with the LIKE Condition
Next, let’s look at an example of how to use the NOT Operator with the LIKE condition.
In this example, we have a table called suppliers with the following data:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 100 | Microsoft | Redmond | Washington |
| 200 | Mountain View | California | |
| 300 | Oracle | Redwood City | California |
| 400 | Kimberly-Clark | Irving | Texas |
| 500 | Tyson Foods | Springdale | Arkansas |
| 600 | SC Johnson | Racine | Wisconsin |
| 700 | Dole Food Company | Westlake Village | California |
| 800 | Flowers Foods | Thomasville | Georgia |
| 900 | Electronic Arts | Redwood City | California |
Let’s look for all records in the suppliers table where the supplier_name does not contain the letter ‘o’. Enter the following SQL statement:
There will be 1 record selected. These are the results that you should see:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 400 | Kimberly-Clark | Irving | Texas |
In this example, there is only one record in the suppliers table where the supplier_name does not contain the letter ‘o’.
Example — Using Escape Characters with the LIKE Condition
It is important to understand how to "Escape Characters" when pattern matching. You can escape % or _ and search for the literal versions instead.
Let’s say you wanted to search for % as a literal in the LIKE condition. You can do this using an Escape character. In our example, we will use ! as the escape character in the LIKE condition.
In this example, we a table called test with the following data:
| test_id | test_value |
|---|---|
| 1 | 10% |
| 2 | 25% |
| 3 | 100 |
| 4 | 99 |
We could return all records from the test table where the test_value contains the % literal. Enter the following SQL statement:
These are the results that you should see:
| test_id | test_value |
|---|---|
| 1 | 10% |
| 2 | 25% |
This example identifies the ! character as an escape character. The first and last % values in the LIKE condition are treated as regular wildcards. The !% is an escaped % so it is treated as a literal % value.
You could further modify the above example and only return test_values that start with 1 and contain the % literal. Enter the following SQL statement:
These are the results that you should see:
| test_id | test_value |
|---|---|
| 1 | 10% |
This example will only return one record this time. Because there is only one test_value that starts with 1 and contains the % literal.
Frequently Asked Questions
Question: How do you incorporate the Oracle UPPER function with the SQL LIKE condition? I’m trying to query against a free text field for all records containing the word "test". The problem is that it can be entered in the following ways: TEST, Test, or test.
Answer: To answer this question, let’s look at an example.
Let’s say that we have a suppliers table with a field called supplier_name that contains the values TEST, Test, or test.
If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SQL SELECT statements:
These SQL SELECT statements use a combination of the Oracle UPPER function and the SQL LIKE condition to return all of the records where the supplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test.
Practice Exercises
If you want to test your skills using the SQL LIKE condition, try some of our practice exercises.
These exercises allow you to try out your skills with the LIKE condition. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer. Give it a try!
Подстановочные знаки в SQL
В этой статье пойдет разговор о подстановочных символах в структурированном языке запросов SQL (structured query language). Понимание работы соответствующего оператора Like позволит вам выполнять специальные запросы и возвращать (return) искомые значения. Будут рассмотрены примеры для системы управления базами данных MS SQL Server.
Подстановочные знаки необходимы для замены любых символов в строке с последующим сравнением и выборкой нужных данных из таблицы. Они используются при составлении запроса. В декларативном языке программирования SQL для этих целей используется специальный оператор Like. В сочетании с ключевым словом WHERE, Like обеспечивает поиск заданного шаблона в необходимом столбце.
Изучив описание и список (List of wildcards) ниже, вы узнаете, какие подстановочные знаки можно использовать с оператором Like:
- «%» — может замещать собой любые значения (ноль и больше);
- «_» — нижнее подчеркивание означает лишь один символ;
- «[]» — здесь следует любой отдельный символ;
- «^» — тоже любой символ, но не заключенный в скобки;
- «-» — через дефис можно прописать целый набор символов, некий интересующий диапазон.
Выше мы рассмотрели подстановочные знаки для MS SQL Server — СУБД от Microsoft. Однако если сравнить системы SQL Server и Access, мы увидим, что схожим образом обстоит ситуация и в случае с базами данных MS Access — они тоже имеют свою систему подстановочных элементов — вот для сравнения List of wildcards для MS Access:
List of wildcards
Также, глядя на вышеуказанные списки, стоит учесть, что все эти элементы можно применять в разнообразных комбинациях.
Однако давайте лучше перейдем к практике: займемся составлением простейших запросов и посмотрим, как Like выполняет возвращение (returning) искомых данных.
Работа Like на примерах MS SQL Server
Для демонстрации работы оператора Like воспользуемся таблицей Customer со следующим содержимым:
Составим инструкцию, которая вернет (returned) из таблицы клиентов (from customers) всех покупателей, имена которых начинаются с буквы «а»:
SELECT * FROM Customer
WHERE FirstName LIKE ‘a%’;
После сравнения и выборки данных клиентов останется всего двое, что соответствует действительности:
Теперь давайте выполним выборку покупателей, в именах которых содержатся буквы «ci». Местонахождение этих букв в слове в нашем случае значения не имеет — главное, чтобы они были:
Мы видим, что оператор Like возвращает (returns) 2 имени. Важно понимать, что не имеет значения, где именно эти символы, ведь % может означать и ноль, то есть указанные символы могут быть и в начале слова, и в середине, и в конце. Чтобы продемонстрировать это, выполним ту же команду, но уже для телефонов. Поместив в шаблон «2», мы увидим, что возвращаются (return) все номера, где встречается цифра 2, причем вне зависимости от места расположения этой двойки:
Теперь поработаем со знаком нижнего подчеркивания. Он означает один и только один любой символ. С его помощью сделаем выборку стран, названия которых заканчиваются на «exico»:
Также учтите, что регистр в составляемом шаблоне значения не имеет, то есть Like сравнивает и возвращает (return) значения без учета регистра:
Теперь немного изменим запрос и задействуем два символа подчеркивания:
После сопоставления данных и отработки запроса мы получим такой же результат.
Дальше — интереснее. Можно выбрать из таблицы все страны, которые начинаются на «S», «F» и «G». Тут пригодятся квадратные скобки и % — то есть мы используем уже комбинацию:
Как видите, все очень даже просто. В следующем предложении выберем уже диапазон значений из нужного столбца, воспользовавшись комбинацией трех подстановочных элементов:
То есть мы вывели все страны, названия которых начинаются с букв A, B или C.
Теперь давайте вспомним, что в программировании существует равно (==) и не равно (!=). По схожей аналогии работает и [charlist]. Если в начале квадратных скобок мы поместим восклицательный знак, произойдет выборка всех данных, которые не отвечают поставленному условию (not). Синтаксис следующий:
Благодаря этому запросу мы получим все города, названия которых НЕ начинаются с букв A, B или C. Но если вернуться к таблицам начала статьи, становится понятно, что это работает лишь для БД MS Access.
SQL Server LIKE
Summary: in this tutorial, you will learn how to use the SQL Server LIKE to check whether a character string matches a specified pattern.
SQL Server LIKE operator overview
The SQL Server LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters. The LIKE operator is used in the WHERE clause of the SELECT , UPDATE , and DELETE statements to filter rows based on pattern matching.
The following illustrates the syntax of the SQL Server LIKE operator:
Pattern
The pattern is a sequence of characters to search for in the column or expression. It can include the following valid wildcard characters:
- The percent wildcard (%): any string of zero or more characters.
- The underscore (_) wildcard: any single character.
- The [list of characters] wildcard: any single character within the specified set.
- The [character-character]: any single character within the specified range.
- The [^]: any single character not within a list or a range.
The wildcard characters makes the LIKE operator more flexible than the equal (=) and not equal (!=) string comparison operators.
Escape character
The escape character instructs the LIKE operator to treat the wildcard characters as the regular characters. The escape character has no default value and must be evaluated to only one character.
The LIKE operator returns TRUE if the column or expression matches the specified pattern.
To negate the result of the LIKE operator, you use the NOT operator as follows:
SQL Server LIKE examples
See the following customers table from the sample database:
The % (percent) wildcard examples
The following example finds the customers whose last name starts with the letter z :
The following example returns the customers whose last name ends with the string er :
The following statement retrieves the customers whose last name starts with the letter t and ends with the letter s :
The _ (underscore) wildcard example
The underscore represents a single character. For example, the following statement returns the customers where the second character is the letter u :
- The first underscore character ( _ ) matches any single character.
- The second letter u matches the letter u exactly
- The third character % matches any sequence of characters
The [list of characters] wildcard example
The square brackets with a list of characters e.g., [ABC] represents a single character that must be one of the characters specified in the list.
For example, the following query returns the customers where the first character in the last name is Y or Z :
The [character-character] wildcard example
The square brackets with a character range e.g., [A-C] represent a single character that must be within a specified range.
For example, the following query finds the customers where the first character in the last name is the letter in the range A through C :
The [^Character List or Range] wildcard example
The square brackets with a caret sign (^) followed by a range e.g., [^A-C] or character list e.g., [ABC] represent a single character that is not in the specified range or character list.
For example, the following query returns the customers where the first character in the last name is not the letter in the range A through X :
The NOT LIKE operator example
The following example uses the NOT LIKE operator to find customers where the first character in the first name is not the letter A :
SQL Server LIKE with ESCAPE example
First, create a new table for the demonstration:
Second, insert some rows into the sales.feedbacks table:
Third, query data from the sales.feedbacks table:
If you want to search for 30% in the comment column, you may come up with a query like this:
The query returns the comments that contain 30% and 30USD, which is not what we expected.
To solve this issue, you need to use the ESCAPE clause:
In this query, the ESCAPE clause specified that the character ! is the escape character. It instructs the LIKE operator to treat the % character as a literal string instead of a wildcard. Note that without the ESCAPE clause, the query would return an empty result set.
In this tutorial, you have learned how to use the SQL Server LIKE operator to check if a character string matches a specified pattern.