Developers and data experts can use SQL to communicate with databases. Thanks to various queries, you can select certain information, update a table, add data, delete data, and so on. Among the commands you need to know is the LIKE SQL query.
But what is it used for? How do you use it? Discover the answers with a few examples of concrete applications.
Definition of LIKE SQL
The LIKE operator is one of the essential commands of the SQL computer language. Its purpose is to search for specific data within a table. To achieve this, LIKE SQL uses templates to define exactly what you need. For example, if you want to select customers whose names start with “de” or end with “in”, etc., you can use the “in” and “out” patterns.
💡Related articles:
How do I use LIKE SQL?
To use the LIKE SQL query, the most common syntax is as follows:
SELECT *
FROM table
WHERE column LIKE model
With this command, you can select data within a table, in a column, according to the model defined by LIKE SQL. But what is this search pattern?
It’s defined by wildcards. These replace the other characters in your string. There are two main wildcards. These are :
%: represents all other characters.
_: represents a single character.
From there, here are the possible combinations:
- LIKE ‘%a’: allows you to search for all strings ending in “a”.
- LIKE ‘a%’: conversely, you can search for all strings beginning with “a”.
- LIKE ‘%a%’: this concerns all records that use the “a” character. In this case, the % may indicate only one, several or even no character before or after the “a”.
- LIKE ‘a%ion’: this pattern searches for strings starting with “a” and ending with “ion”, such as “airplane” or “attention”.
- LIKE ‘a__’: this searches for all strings beginning with “a”. But the total number of characters is limited to 3.
- LIKE ‘_a%’: the combination of these two wildcards tells us that the total number of characters doesn’t matter. However, the “a” must be placed in the second position.
To help you better understand the use of LIKE SQL and its various search models, we’ve provided a number of concrete examples.
How do I use LIKE SQL?
To use the LIKE SQL query, the most common syntax is as follows:
SELECT *
FROM table
WHERE column LIKE model
With this command, you can select data within a table, in a column, according to the model defined by LIKE SQL. But what is this search pattern?
It’s defined by wildcards. These replace the other characters in your string. There are two main wildcards. These are :
%: represents all other characters.
_: represents a single character.
From there, here are the possible combinations:
- LIKE ‘%a’: allows you to search for all strings ending in “a”.
- LIKE ‘a%’: conversely, you can search for all strings beginning with “a”.
- LIKE ‘%a%’: this concerns all records that use the “a” character. In this case, the % may indicate only one, several or even no character before or after the “a”.
- LIKE ‘a%ion’: this pattern searches for strings starting with “a” and ending with “ion”, such as “airplane” or “attention”.
- LIKE ‘a__’: this searches for all strings beginning with “a”. But the total number of characters is limited to 3.
- LIKE ‘_a%’: the combination of these two wildcards tells us that the total number of characters doesn’t matter. However, the “a” must be placed in the second position.
To help you better understand the use of LIKE SQL and its various search models, we’ve provided a number of concrete examples.
5 examples of using the SQL LIKE query
For these examples, we will use the “Customer” table below:
| Last Name | First Name | Postal Code | Phone Number | |
|---|---|---|---|---|
| Martin | Annie | 75112 | 06.40.45.36.61 | annie.m@gmail.com |
| Durand | Fabien | 17042 | 07.58.36.21.63 | fabien.durand@laposte.net |
| Dupont | Thomas | 17015 | 06.23.98.51.45 | dupont17@gmail.com |
| Berger | Justine | 77485 | 06.15.29.85.61 | jusber@orange.fr |
| Bernard | Valentin | 63950 | 07.59.65.19.95 | beber@sfr.fr |
LIKE ‘a%’
For this LIKE SQL command, we’re looking for all customers living in the Charente-Maritime department (17). Here’s the query syntax:
SELECT *
FROM Customer
WHERE Postal code LIKE 77%
And here’s the result:
| Last Name | First Name | Postal Code | Phone Number | |
|---|---|---|---|---|
| Durand | Fabien | 17042 | 07.58.36.21.63 | fabien.durand@laposte.net |
| Dupont | Thomas | 17015 | 06.23.98.51.45 | dupont17@gmail.com |
LIKE ‘a%’
The aim of this LIKE SQL query is to find all customers with a Gmail address. You’ll need this syntax:
SELECT *
FROM Customer
WHERE email LIKE %gmail.com
And here’s the result:
| Last Name | First Name | Postal Code | Phone Number | |
|---|---|---|---|---|
| Martin | Annie | 75112 | 06.40.45.36.61 | annie.m@gmail.com |
| Dupont | Thomas | 17015 | 06.23.98.51.45 | dupont17@gmail.com |
LIKE ‘%a%’
Here, we’re looking for all customers whose first name contains an “i”. The syntax is as follows:
SELECT *
FROM Customer
WHERE first name LIKE %i%
And here’s the result:
| Last Name | First Name | Postal Code | Phone Number | |
|---|---|---|---|---|
| Martin | Annie | 75112 | 06.40.45.36.61 | annie.m@gmail.com |
| Durand | Fabien | 17042 | 07.58.36.21.63 | fabien.durand@laposte.net |
| Berger | Justine | 77485 | 06.15.29.85.61 | jusber@orange.fr |
| Bernard | Valentin | 63950 | 07.59.65.19.95 | beber@sfr.fr |
LIKE ‘_a%’
For this LIKE SQL query, we’re looking for all customers whose second letter in their first name is an “ai”. Here’s the syntax to use:
SELECT *
FROM Customer
WHERE first name LIKE %_a%
And here’s the result:
| Last Name | First Name | Postal Code | Phone Number | |
|---|---|---|---|---|
| Durand | Fabien | 17042 | 07.58.36.21.63 | fabien.durand@laposte.net |
| Bernard | Valentin | 63950 | 07.59.65.19.95 | beber@sfr.fr |
LIKE ‘a%ion’
For this last LIKE SQL example, we’re looking for all customers whose phone number starts with 06 and ends with 61. The syntax is as follows:
SELECT *
FROM Customer
WHERE phone number LIKE 06%61
And here’s the result:
| Last Name | First Name | Postal Code | Phone Number | |
|---|---|---|---|---|
| Martin | Annie | 75112 | 06.40.45.36.61 | annie.m@gmail.com |
| Berger | Justine | 77485 | 06.15.29.85.61 | jusber@orange.fr |
