SQL Wildcard Searches using SQL LIKE

By: Joe Gavin | Updated: 2023-05-12 | Comments | Related: > TSQL

Problem

How do I find records in a SQL Server database table if I only know part of the string I'm looking for? If I want to know all records that begin, end, or contain a specific string of characters? If I want to know all records that do not begin, end, or contain a certain string of characters?

Solution

To solve these issues, we use SQL wildcards. Wildcards are used by the LIKE operator and let us substitute for characters. We'll look at several examples for SQL databases in this tutorial.

LIKE Operator Syntax

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

SQL Wildcard Characters

%	Zero or more characters
_	Single character
[]	Any character in the bracket
[-]	A single character in a range between the '-'
[^]	Any character not in a bracket

AdventureWorks2019 Sample Database View

We'll use the HumanResources.vEmployee view in the free AdventureWorks2019 sample database for our examples.

The following examples will look at these fields in the employee contact view.

-- employee contact info 
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
ORDER BY [LastName];
GO

SQL Wildcard Search Examples with SQL LIKE

Percent Sign Wildcard

Probably the most used wildcard is the percent sign (%) in a SQL query.

This SELECT query returns any record where the last name begins with 'br' and has any combination of 0 or more characters following it.

-- employee contact info for all employees with the last name beginning with 'br' based on the WHERE clause
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE 'br%'
ORDER BY [LastName];
GO

Bradley, Brewer, and Brown meet the criteria.

Next, we'll query for records where the last name begins with 'br', has any combination of 0 or more characters following it as before, but only those ending with 'n'.

-- employee contact info for all employees with the last name beginning with 'br' and ending with 'n'
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE 'br%n'
ORDER BY [LastName];
GO

Records with the last name Brown are returned.

The % wildcard can go at the beginning, end, or middle of a string. Here we put it at the beginning of the string to return the records where the last name begins with any one or more characters and ends in 'own'.

-- employee contact info for all employees with the last name beginning with anything and ending with 'own'
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE '%own'
ORDER BY [LastName];
GO

Records with the last name Brown meet the criteria.

Underscore Wildcard

The underscore (_) wildcard replaces any one single character.

We want to see employee contact information for employees whose last name begins with 'brow' and ends with any 1 character.

-- employee contact info for all employees with the last name beginning with 'brow' and ending with any 1 character
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE 'brow_' -- 1 underscore
ORDER BY [LastName];
GO

Underscores can be used next to each other. Here, we will get records where the last name begins with 'bro' and ends with any 2 characters.

-- employee contact info for all employees with the last name beginning with 'bro' and ending with any 2 characters
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE 'bro__' -- 2 underscores
ORDER BY [LastName];
GO

As with the percent wildcard, the underscore wildcard can be used at the beginning, end, or middle of a string. This query returns records where the last name begins with 'br', ends with 'wn', and has any singular character in the middle.

-- employee contact info for all employees with the last name beginning with 'br', 1 any 1 character in the middle and ending with 'wn'
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE 'br_wn' -- 1 underscore in middle
ORDER BY [LastName];
GO

Bracket Wildcards

Characters that meet the criteria you're searching for are placed within the brackets ([]). We'll search for last names that begin with 'br', end with 'wn', and have an 'a', 'e', 'i', 'o', or 'u' in the middle.

-- employee contact info for all employees with the last name beginning with 'br', a vowel in the middle and ending with any 'wn'
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE 'br[a e i o u]wn'
ORDER BY [LastName];
GO

You can also search for one in a range of characters within the square brackets. This will return records with last names beginning with any single character beginning with 'a' through 'm' and ending with 'rown'.

-- employee contact info for all employees with the last name beginning with any letter in the first half of the alphabet and ending with any 'rown'
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE '[a-m]rown'
ORDER BY [LastName];
GO

Not Like

This will return records where the last name does not begin with 'br' and ends with 0 or more characters.

-- employee contact info for all employees with the last name not beginning with 'br'
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] NOT LIKE 'br%' -- NOT Operator
ORDER BY [LastName];
GO

We use a caret (^) if we want records that do not match the pattern when using []. Now, we look for the last name that begins with 'br', ends with 'wn', and has any letter other than 'a', 'e', 'i', 'o', or 'u' in the middle.

-- employee contact info for all employees with the last name beginning with 'br', a vowel in the middle and ending with any 'wn'
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [LastName] LIKE 'br[^a e i o u]wn'
ORDER BY [LastName];
GO

As expected, no records are returned as it's unlikely a last name would not have at least one vowel.

Escaping Wildcards

What if the string of part of the string you're searching on is a wildcard? We'll attempt to search for all email addresses that have an underscore in the name.

-- search for records with underscore in first name of email
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [EmailAddress] LIKE '%_%@adventure-works.com'
ORDER BY [LastName];
GO

This query will interpret the string before the @ symbol as beginning with any 0 or more characters, ending with any zero or more characters with any 1 character in the middle followed return every row in the view.

We simply need to define an escape character with the ESCAPE clause.

-- search for records with underscore in first name of email by escaping underscore
SELECT [LastName]
      ,[MiddleName]
      ,[FirstName]
      ,[PhoneNumber]
      ,[PhoneNumberType]
      ,[EmailAddress]
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
WHERE [EmailAddress] LIKE '%\_%@adventure-works.com' ESCAPE '\' 
ORDER BY [LastName];
GO

The query will now interpret the string before the @ symbol as beginning with any 0 or more characters, ending with any 0 or more characters with an underscore in the middle, and returning just the rows we're looking for.

Next Steps

Here are some more MSSQLTips articles with more examples of using SQL wildcards:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

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

View all my tips

Article Last Updated: 2023-05-12

SQL Wildcard Examples using SQL LIKE