SQL WHERE LIKE to Construct More Efficient Queries

By:   |   Updated: 2023-05-01   |   Comments (1)   |   Related: More > 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 Wildcard Description Sample
% Any string value, date, number, etc., of any length WHERE LastName LIKE 'A%' finds all names that start with A
_ (Underscore symbol) Represents a single character, space, number, etc. Use two or more to represent more than one character WHERE LastName LIKE 'A_b' or WHERE LastName LIKE 'A__r'
[] An array of characters in a specified range. Like [a-f] or [abcdef] WHERE LastName LIKE 'A[a-f]' will return all last names that start with A, and the second letter is any letter in the array [abcdef]
[^] Specifies any characters that are NOT in the specified array WHERE LastName LIKE 'A[^abcdef]' will not return any names whose second letter is within the array

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.

all the rows where the first name starts with an R

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

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

Results:

all rows where the first name ends with that letter

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:

all rows where the last name starts with an H and has any value for the second character

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:

any row where the last name ends with a U and starts with any character

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:

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

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)

only the dates for 2013

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:

all the rows that have a phone number with a 330 area code

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)

all rows that have 555 as the prefix in our phone number list

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)

all phone numbers that do not have just an area code followed by the seven-digit phone number

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)

any rows where the last name starts with a J and has a second letter that is like A, C, or K

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)

reverse previous by adding a ^
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aubrey Love 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 PCs when they were introduced to the public in the late 70's.

View all my tips


Article Last Updated: 2023-05-01

Comments For This Article




Friday, June 2, 2023 - 4:47:09 AM - Thomas Franz Back To Top (91245)
Nice article for beginners, BUT

NEVER ever filter a date with LIKE '%2023%'. It would cause a whole table / index scan and needs to implicit convert every date to string before applying the (slow) LIKE condition. Just be not so lazy and use WHERE [my_date] >= '20230101' AND [my_date] < '20240101'. If it is really a date (and not a DATETIME), you could use BETWEEN '20230101' AND '20231231' too (but with DATETIME this would not include rows after 0:00 at the 31.12.).

And WHERE LastName LIKE 'J[ack]%'; is a bad example. Yes, of course it returns you all the Jacksons, but an unexperienced beginner would believe, that this is because of the [ack] and don't realize that this would return Jason and Jameika or "JK & Co. Company" too.