MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

A Complete Guide to the SQL LIKE Operator

intro

Let's learn everything you need to know about the SQL LIKE operator to become an expert in pattern matching within databases.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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:

Copy
        
1 SELECT column_1, column_2, ..., column_N 2 FROM table_name 3 WHERE column_X LIKE pattern

Where:

  • column_X is the name of the column you want to apply pattern matching on. This column can be of any SQL data type that the DBMS can implicitly cast to a string;
  • pattern is the string or numerical pattern you want to match against.

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:

  • % (percent sign): Represents zero, one, or multiple characters.
  • _ (underscore): Represents a single character.

Take a look at the sample patterns below to better understand how the wildcard characters work:

  • 'A%': Matches any string starting with “A”, followed by zero or more other characters.
  • '%ing': Matches any string ending with “ing”.
  • '%data%': Matches any string containing “data” anywhere within it.
  • '_at': Matches any string with three characters ending with 'at'. (e.g., "cat", "bat", etc.)
  • 'H_ll_': Matches any string with five characters, where the first character is 'H', the third character is 'l', and the fourth character is 'l' (e.g., "Hello").
  • '%_%': Matches any string containing at least one character.

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 “Data” view of DbVisualizer to explore the data stored in a table
The "Data" view of DbVisualizer to explore the data stored in a table

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:

Copy
        
1 SELECT * FROM customers 2 WHERE last_name LIKE 'Smith';

The result will be:

Note the last_name value of the selected column
Note the last_name value of the selected column

Semantically, that is equivalent to:

Copy
        
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”:

Copy
        
1 SELECT * FROM customers 2 WHERE first_name LIKE 'J%';

The result set will be:

All customer’s first names start with J
All customer's first names start with J

Similarly, you can get all customers whose postal code starts with the number 9:

All postal code start with 9
All postal code start with 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:

Copy
        
1 SELECT * FROM customers 2 WHERE address LIKE '%Lane';

The LIKE SQL query above will retrieve these rows:

All addresses end with the word Lane
All addresses end with the word Lane

Contains

Here is how to use the LIKE operator in SQL to find customers whose address contains the “ne” substring:

Copy
        
1 SELECT * FROM customers 2 WHERE address LIKE '%ne%';

Launch on customers, and you will get:

All customer addresses contain the word ne
All customer addresses contain the word ne

Has N Characters

Assume you want to find all customers whose first name has exactly four characters:

Copy
        
1 SELECT * FROM customers 2 WHERE first_name LIKE '____';

The result will be:

All first names have 4 characters
All first names have 4 characters

Semantically, the above query is equivalent to:

Copy
        
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:

Copy
        
1 SELECT * FROM customers 2 WHERE last_name LIKE 'M_____%';

The resulting records would be:

The result set matches the specified criteria
The result set matches the specified criteria

Equivalently, you could achieve the same result with:

Copy
        
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:

  • Prefer specific, concise, to-the-point patterns over broad, long, inefficient patterns.
  • If you frequently search on a particular column using the LIKE statement, consider creating an index on that column to improve query performance.
  • Familiarize yourself with the behavior of wildcard characters like “%” and “_”, and the ones supported by your specific database system.
  • Do not forget that the LIKE statement may be case-sensitive, depending on the database collation.
  • Before deploying queries using the LIKE statement in a production environment, test their performance with realistic data volumes to identify any potential bottlenecks.
  • Some LIKE queries tend to be inefficient, so consider using other string functions like LEFT(), RIGHT(), LENGTH() or SUBSTRING() to achieve the same result more efficiently.
  • Try to avoid a leading wildcard (e.g., "%abc") as it might prevent the query optimizer from utilizing indexes efficiently. A leading wildcard prevents the query optimizer from utilizing indexes because indexes can only be used when your database knows what exactly you’re searching for (i.e. if the operators “=”, “<=”, or “>=”) are in use.
  • For more complex filtering requirements, consider using pattern matching based on regular expressions.

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:

Copy
        
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:

Copy
        
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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL STUFF: Insert a String Into Another in SQL Server

author Antonello Zanini tags SQL SERVER 7 min 2024-07-18
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

Types of Databases Explained

author Lukas Vileikis tags MySQL 6 min 2024-07-01
title

MySQL ALTER TABLE Explained

author Lukas Vileikis tags MySQL SQL 7 min 2024-06-27
title

Error: MySQL Shutdown Unexpectedly. Causes & Solutions

author Lukas Vileikis tags MySQL SQL 4 min 2024-06-24
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

MySQL IFNULL - Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-06-13

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗