intro
Let's learn everything you need to know about the SQL LIKE operator to become an expert in pattern matching within databases.
Retrieving the required records from a table with a single query is not always easy. Simple WHERE conditions may not be enough, especially when searching for string patterns within your data. This is where the SQL LIKE operator comes in!
In this guide, you will find out what the LIKE SQL operator is, how pattern matching works in SQL, and how to use it in several real-world scenarios. Time to learn how to filter data like a boss!
What Is the LIKE Operator in SQL?
LIKE is an SQL operator to search for a specified pattern in a column. In particular, it is generally used in the WHERE clause of a SELECT statement to filter rows by applying pattern matching in SQL.
The SQL LIKE operator is part of the ANSI/ISO standard, and most popular database systems support it. Refer to their official documentation pages to learn more:
LIKE enables you to search for strings that match a specified pattern, which can include wildcard characters representing one or more characters. This opens the door to flexible and powerful data querying.
How To Use LIKE in SQL
This is the syntax of a complete SQL LIKE statement:
1
SELECT column_1, column_2, ..., column_N
2
FROM table_name
3
WHERE column_X LIKE pattern
Where:
String patterns can also include wildcard characters. These allow you to represent any character or sequence of characters within the pattern. Here is where it lies the true power of the LIKE SQL operator and what makes it so popular.
Each DBMS supports its own set of wildcard characters, but the two most popular are:
Take a look at the sample patterns below to better understand how the wildcard characters work:
Note: By default, the LIKE operator in SQL is usually case-sensitive. In other words, patterns distinguish between uppercase and lowercase characters. However, the case sensitivity of LIKE depends on your database collation. If the database collation is case-insensitive, LIKE will also be case-insensitive.
SQL LIKE Operator: Use Cases
Now that you know what databases support the LIKE SQL operator and how it works, you are ready to explore its main use cases.
The sample queries in this section will be executed on the customers MySQL table below:
The queries will be executed in DbVisualizer—the database client with the highest user satisfaction in the market—but any other database client will do.
Equality
An SQL LIKE statement without wildcards works just like the equality operator =.
Suppose you want to retrieve all customers with the “Smith“ last name. This is how you can achieve that using LIKE:
1
SELECT * FROM customers
2
WHERE last_name LIKE 'Smith';
The result will be:
Semantically, that is equivalent to:
1
SELECT * FROM customers
2
WHERE last_name = 'Smith';
At the same time, the two queries may result in two different execution plans and lead to different performance.
Starts With
This is how you can customers whose first name starts with the letter “J”:
1
SELECT * FROM customers
2
WHERE first_name LIKE 'J%';
The result set will be:
Similarly, you can get all customers whose postal code starts with the number 9:
Keep in mind that LIKE also works on non-string columns that the database can implicitly convert to string values.
Ends With
If you want to find customers whose address ends with the “Lane” word, this is how you can do it:
1
SELECT * FROM customers
2
WHERE address LIKE '%Lane';
The LIKE SQL query above will retrieve these rows:
Contains
Here is how to use the LIKE operator in SQL to find customers whose address contains the “ne” substring:
1
SELECT * FROM customers
2
WHERE address LIKE '%ne%';
Launch on customers, and you will get:
Has N Characters
Assume you want to find all customers whose first name has exactly four characters:
1
SELECT * FROM customers
2
WHERE first_name LIKE '____';
The result will be:
Semantically, the above query is equivalent to:
1
SELECT * FROM customers
2
WHERE LENGTH(first_name) = 4;
Has At Least N Characters
If you want to get all customers whose last name has at least 6 characters and the first character is “M”, this is the query you should write:
1
SELECT * FROM customers
2
WHERE last_name LIKE 'M_____%';
The resulting records would be:
Equivalently, you could achieve the same result with:
1
SELECT * FROM customers
2
WHERE last_name LIKE 'M%' and LENGTH(last_name) >= 6;
Note that you can combine SQL LIKE wildcard characters to write complex patterns.
Best Practices for the SQL LIKE Statement
Below are the best practices you should keep in mind when using the LIKE SQL operator:
Conclusion
In this guide, you’ve understood what the LIKE SQL operator is, which databases support it, and how it works. Now you know that it helps you filter records based on pattern matching. Thanks to the examples explored here, you also understood when and how to use LIKE in SQL.
Performing complex data querying becomes easier with a database client. DbVisualizer is a powerful database client that supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free today!
FAQ
Is it possible to combine the functionality of IN and LIKE in a WHERE clause?
While it is not possible to directly combine the functionality of IN and LIKE in a WHERE clause, you can achieve the same result with a couple of workarounds. The idea is to use LIKE for multiple values or multiple words.
The first approach involves using the OR operator to combine more LIKE conditions on the same column:
1
SELECT * FROM table_name
2
WHERE column_name LIKE '%pattern1%' OR column_name LIKE '%pattern2%' OR column_name LIKE '%pattern3%';
The second approach relies on the LIKE ANY operator supported by some database systems, such as PostgreSQL. That operator allows you to compare a value to a set of patterns:
1
SELECT * FROM table_name
2
WHERE column_name LIKE ANY (ARRAY['%pattern1%', '%pattern2%', '%pattern3%']);
Discover more in the dedicated Stack Overflow thread.
Is it possible to use a regex in a LIKE SQL statement?
No, the standard SQL LIKE statement does not support regex. While it allows some special wildcards, it does not support full regular expressions as patterns. SQL Server's LIKE supports the [] and [^] wildcards for more complex patterns. Still, it does not support regular regex.
What is the opposite of the LIKE SQL operator?
The opposite of the LIKE operator is the NOT LIKE SQL operator. While LIKE is used to match patterns, NOT LIKE is used to exclude rows that match the specified pattern. The idea is to use it in conjunction with the WHERE clause to filter out rows that do not meet the specified pattern criteria.
What are the SQL LIKE wildcard values available?
The default SQL LIKE wildcard values are '%' and '_'. Some databases may offer additional wildcard characters or variations for more specific pattern-matching needs. For example, SQL Server also has [] and [^], which work just like in regular expressions. Always refer to the documentation of your specific database system for full details.
How to ensure that LIKE SQL queries will use an index defined on my table?
To make queries using the LIKE SQL operator benefit from using SQL indexes, aim to build your SQL queries in such a way that searches for an exact match. Make sure that the column you’re using the LIKE operator on has an index, then search for data using “=”, “<=”, or “>=” operators.
Is the LIKE operator case-insensitive in SQL?
By default, LIKE is case-sensitive in most database systems. However, the case sensitivity of the operator depends on the collation of the table it is applied on.
