SQL WHERE LIKE to Construct More Efficient Queries

By: Aubrey Love   |   Updated: 2023-05-01   |   Comments (3)   |   Related: > TSQL

Problem

If you're a SQL developer, then you realize that the WHERE clause is one of the most commonly used clauses in SQL queries. It's often necessary to fetch rows from a table based on certain conditions where the search value is a person's name or job title. But what about when you need to search for records with similar patterns or values and are unsure of the full value? For example, I want to search for all rows with the first name Aubrey but am unsure how to spell it.

Solution

That's where the WHERE LIKE clause comes in. In this SQL tutorial, we will discuss the purpose and use of the WHERE LIKE clause in SQL Server and how it can help you construct more efficient queries. We'll also provide some examples to help illustrate when and how to use this clause. So read on to learn more about this helpful tool and how it can assist with your SQL development work!

SQL Server WHERE LIKE Overview

The LIKE operator in SQL Server is used to compare a character string against a pattern. This pattern can include regular characters and wildcard characters. The LIKE operator is often used in the WHERE clause of a SQL statement to find rows that match a specified pattern.

SQL Server WHERE LIKE Basic Syntax

-- Syntax for SQL Server and Azure SQL Database
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Okay, that's the basic syntax according to Microsoft's BOL (Books Online). In the real world, I prefer seeing something like the following SQL example to understand it better:

SELECT
   column1,
   column2,
   column3
FROM tableName
WHERE columnName LIKE 'YourValue';
GO

Unlike the comparison operators that require an exact match, the SQL WHERE LIKE condition allows you to return rows where a specific value or values are found anywhere in the search criteria. We can do this by using SQL wildcards.

SQL LIKE Wildcards

When working with WHERE LIKE, you will most often incorporate one of the following wildcards to assist in refining your search:

SQL LIKE Wildcard Examples

In the following sections, we show, by example, how to use wildcards to filter out specific names from a database table. You can easily do these examples with numbers, dates, etc. The only place you cannot use wildcards is in a search against a Boolean value since it is a simple 1 or 0 (one or zero) singular value.

For the following SQL syntax, we will create a test table to work with and populate it with some generic data from the AdventureWorks2019 sample database:

Use this script below to create a test table:

CREATE TABLE [dbo].[ TestTable1](
   [colID] [int] IDENTITY(1,1) NOT NULL,
   [firstName] [varchar](20) NULL,
   [lastName] [varchar](20) NULL
) ON [PRIMARY]
GO

Now, use the following code to add data to the test table:

INSERT INTO TestTable1(firstName, lastName)
VALUES
('Theodore','Serrano'),
('Rachel','Jones'),
('Omar','Hu'),
('Morgan','Smith'),
('Christina','Rivera'),
('Devin','Ramirez'),
('Shirley','Bruner'),
('Micah','Zeng'),
('Deanna','Ruiz'),
('Ryan','Diaz'),
('Marcus','Phillips'),
('Brad','Yuan');
GO

SQL WHERE LIKE Using the % Wildcard

In this SELECT statement, we will find all the rows where the first name starts with an "R".

SELECT *
FROM TestTable1
WHERE firstName LIKE 'R%';
GO

As you can see, this returned all rows where the first name starts with an R and has any number of characters following it. Likewise, we can move our % wildcard to the position before the desired letter and return all rows where the first name ends with that letter.

Here is another example where first name needs to end with a "y".

SELECT *
FROM TestTable1
WHERE firstName LIKE '%y';
GO

Results:

SQL WHERE LIKE Using the _ (Underscore) Wildcard

The underscore works slightly differently than the % wildcard because it represents only one character at a time. In our sample below, we will return all rows where the last name starts with an H and has any value for the second character.

SELECT *
FROM TestTable1
WHERE lastName LIKE 'H_';
GO

Results:

Like with the % wildcard, we can reverse this pattern to return any row where the last name ends with a U and starts with any character.

SELECT *
FROM TestTable1
WHERE lastName LIKE '_U';
GO

Results:

Remember, the underscore represents only one character in a string. In the example above, if the name had more than two letters, it would return no records. No other records have only two characters in the last name.

SQL WHERE LIKE Combining the % and _ (Underscore) Wildcards

We can also combine the % and underscore wildcards to enhance our filters. SQL wildcards allow for combining and having multiple wildcards in search patterns. In the following example, we will combine two underscores and a % wildcard to return any rows where the first name contains any characters in the first and second position, a "C" as the third character, and any characters afterward.

SELECT *
FROM TestTable1
WHERE firstName LIKE '__c%';
GO

Results:

Notice that both first names have the letter C as the third character but different characters before and after that character.

Take a moment to experiment with these wildcards and get familiar with their use. Remember that each underscore only represents a single character.

SQL WHERE LIKE to Filter Dates

In the previous two examples, we used a SQL wildcard to filter rows that contained string values. In the following examples, we will filter based on dates and numbers. We will also use the AdventureWorks2019 sample database.

In the "Person.PersonPhone" table, we will query only the dates for 2013. Of the 19,972 rows, our results should only return 8,785 rows.

USE AdventureWorks2019;
GO
 
SELECT ModifiedDate
FROM Person.PersonPhone
WHERE ModifiedDate LIKE '%2013%';
GO

Results: (Partial)

You may have noticed that we put a % sign in front of the four-digit year when there appears to be nothing before that value. This is often done to account for any possible blank spaces we did not anticipate.

Note: as mentioned in a comment for this article, this is not the best approach to filter dates. This will cause table scans or index scans and therefore not a best practice for large datasets.

Now, let's apply the same principle to a number value. In this example, still using the AdventureWorks2019 sample database, we want to return all the rows with a phone number with a 330 area code.

USE AdventureWorks2019;
GO
 
SELECT *
FROM Person.PersonPhone
WHERE PhoneNumber LIKE '330%';
GO

Results:

We could also combine different wildcards, as we did earlier, to return all rows that have 555 as the prefix in our phone number list. In this scenario, we want to use four underscores that represent the three-digit area code and hyphen and will tack the percent wildcard on the end of our script to catch any other values.

USE AdventureWorks2019;
GO
 
SELECT *
FROM Person.PersonPhone
WHERE PhoneNumber LIKE '____555%';
GO

Results: (Partial)

SQL WHERE NOT LIKE Filter

In this example, we will query all phone numbers that do not have an area code followed by the seven-digit phone number. This is how to filter all the international numbers into a single list.

USE AdventureWorks2019;
GO
 
SELECT *
FROM Person.PersonPhone
WHERE PhoneNumber NOT LIKE '____555%';
GO

Results: (Partial)

Notice that there are more than four characters (numbers, hyphens, spaces, etc.) before the 555 prefixes. As we mentioned earlier, the underscore represents one and only one character, number, space, etc.

SQL WHERE LIKE Clause with an Array

In this section, we want to filter by an array of values, not just single characters. An array in SQL can be anything between two numbers or dates or even a list of different values. For example, we could use the WHERE LIKE clause to filter all values between A and F or any number value between 10 and 20. We can also filter by anything or everything in a list, such as 'J[abc]%'.

This will return any rows where the last name starts with a J and has a second letter like A, B, or C. Below is a sample of this in action. We will again be using the AdventureWorks2019 sample database and querying the Person.Person table.

USE AdventureWorks2019;
GO
 
SELECT LastName, FirstName
FROM Person.Person
WHERE LastName LIKE 'J[abc]%';
GO

Results: (Partial)

We can also reverse this by adding a ^ (carrot) symbol to the equation. Note: The carrot symbol can be found on the number 6 key on your keyboard. Just press Shift + 6.

USE AdventureWorks2019;
GO
 
SELECT LastName, FirstName
FROM Person.Person
WHERE LastName LIKE 'J[^abc]%';
GO

Results: (Partial)

Next Steps

• Check out these related tips:
  • SQL LIKE Statement
  • SQL Server LIKE Syntax with Wildcard Characters
  • SQL LIKE Statement for Various Text Patterns
• Reference Links:
  • https://www.essentialsql.com/sql-where-like/
  • https://www.studymite.com/sql/where-like-clause-in-sql
  • https://www.educba.com/sql-like-clause/

About the author

Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips