SQL Server LIKE Syntax with Wildcard Characters

By:   |   Updated: 2021-02-02   |   Comments (3)   |   Related: More > TSQL


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don't necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE <<

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
Problem

Have you ever used the SQL Server LIKE operator and were surprised with the results? Are you sure which strings you need to use to match to a specific pattern? In this article, we will show how to build SQL Server queries including the LIKE operator in the WHERE clause with a pattern containing wildcard characters along with a function you can use to make this easier for your string matches.

Solution

Let's take a look at an example of using the LIKE operator and the unexpected results we get and then how to solve this search pattern problem.

Create Sample Data to Test SQL Server LIKE Operator

Let's start by creating a test environment with a database, table, columns with nvarchar, varchar, nchar or char data types and INSERT character strings:

USE master
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE myUser 
(
   LoginName NVARCHAR(50)
)

INSERT INTO myUser (LoginName) 
VALUES ('newUser'), 
       ('user1'),
       ('_myUser1'),
       ('myUser2'),
       ('_myUser3'),
       ('1MyUser4'),
       ('new%user'),
       ('%newuser%'),
       ('my[user]'),
       ('my[user]1'),
       ('myuser'),
       ('oldUser^/'),
       ('NewUser|'),
       ('User[5'),
       ('user]6')

Thus, we have the following 15 rows in myUser table:

USE TestDB
GO

SELECT *
FROM myUser
query result set

Simple SQL Server T-SQL LIKE Syntax

Let's assume that we want to find all logins that start with the '_' symbol in the WHERE clause. Try to predict the results of the following query before executing the SQL statement with the LIKE condition: 

USE TestDB
GO

SELECT *
FROM myUser
WHERE LoginName LIKE '%_my%'

Does the below output match what you thought you would see?

query result set

Surprised? Why is '1MyUser4' included if it does not start with '_'?

SQL Server T-SQL Wildcard Characters

The answer to the last question in the previous paragraph is that underscore ('_') is not a regular character for the LIKE clause, but a wildcard character. Unlike literal characters, wildcard characters have specific meaning for the LIKE operator.

Below is a list of wildcard characters according to Microsoft's documentation:

wilcard characters

Hence, underscore ('_') in LIKE does not mean a specific regular character, but any single character. This is why '1MyUser4' is included. Moreover, '_myUser1' and '_myUser3' are included for the same reason and not due to matching underscores in the rows and in the pattern.

Using Wildcards Characters with LIKE in T-SQL

Wildcard characters enclosed in the brackets are considered literal characters for pattern matching, so we can rewrite the query in the example in the following way to get the correct result:

USE TestDB
GO

SELECT *
FROM myUser
WHERE LoginName LIKE '%[_]my%'

In this query we are dictating to the query engine that we need to find all logins starting with the '_my' string (but not with any symbol followed by 'my' string):

query result set

By running the queries below you will see the difference between using other wildcard characters in the LIKE clause enclosed in the brackets and without (in the last query it is not necessary to include '[' in the brackets):

USE TestDB
GO

-- %
SELECT *
FROM myUser
WHERE LoginName LIKE '%%%'

SELECT *
FROM myUser
WHERE LoginName LIKE '%[%]%'

-- [
SELECT *
FROM myUser
WHERE LoginName LIKE '%[%'

SELECT *
FROM myUser
WHERE LoginName LIKE '%[[]%'

-- ]
SELECT *
FROM myUser
WHERE LoginName LIKE '%]%'

However, in some situations, it is not convenient to include wildcard characters in brackets. For example, it is possible to have many wildcard characters in the pattern or we can receive the pattern as a parameter.

Using a Parameter to Store a Value for LIKE in T-SQL

In the following example we are declaring a variable and using it as a pattern:

USE TestDB
GO

DECLARE @myUser NVARCHAR(50) = '_my'

SELECT *
FROM myUser
WHERE LoginName LIKE '%'+ @myUser + '%'

The result is the same as in the example where '_' was considered a wildcard character:

query result set

To get around this, we can use the ESCAPE clause with the SQL LIKE operator to tell the query engine to use the wildcard character as a literal.

The ESCAPE clause has the following format:

ESCAPE 'escape_character'

For instance, in the following query the escape character is '!', and it is also included in the pattern. It means the symbol following the escape character should be considered as a regular symbol:

USE TestDB
GO

SELECT *
FROM myUser
WHERE LoginName LIKE '%!_my%' ESCAPE '!'

Therefore, '_' symbol, which is after '!' in the pattern, is not considered a wildcard character and we have this result:

query result set

So, after applying the ESCAPE clause to the example with a variable we have the correct result:

USE TestDB
GO

DECLARE @myUser NVARCHAR(50) = '_my'

SELECT REPLACE(@myUser, '_','|_') AS 'AfterAddingEscapeCharacter'

SELECT *
FROM myUser
WWHERE LoginName LIKE '%' + REPLACE(@myUser, '_','|_') + '%' ESCAPE '|'
query result set

Create SQL Server T-SQL Function for LIKE Escape Clause

To facilitate the routine, a function can be created that will prepare a string for using in the LIKE operator with an ESCAPE clause. This function will consider all possible wildcard characters which can affect the query result:

USE TestDB
GO

CREATE FUNCTION udfReplaceWildcards(@myValue NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN

   /*-----------------------------------------------------------------------------------              
   @myValue - the value that will be used in the LIKE operator with an ESCAPE clause
   -----------------------------------------------------------------------------------*/

   --Replacing wildcard characters
   SET @myValue = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@myValue, '|','||'),'%','|%'),'_','|_'),'[','|['),']','|]')
   
   RETURN IsNull(@myValue,'')
END

In this function, we have used '|' as an escape character and you can see we used '||' to replace '|' so we get the correct results.

After calling the function for '_my' parameter and using the same logic as in the previous example, we will have the same correct result:

USE TestDB
GO

DECLARE @myUser NVARCHAR(50) = '_my' 

SELECT dbo.udfReplaceWildcards(@myUser) AS 'AfterAddingEscapeCharacter'

SELECT *
FROM myUser
WWHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'
query result set

Testing T-SQL Function with Different Wildcards

Below we are testing this function using different wildcards in a specified pattern:

USE TestDB
GO

-- _
DECLARE @myUser NVARCHAR(50) = '_'

SELECT *
FROM myUser
WHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'

-- %
SET @myUser  = '%'

SELECT *
FROM myUser
WHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'

-- [
SET @myUser  = '['

SELECT *
FROM myUser
WHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'

-- ]
SET @myUser = ']'

SELECT *
FROM myUser
WWHERE LoginName LIKE '%' + dbo.udfReplaceWildcards(@myUser) + '%' ESCAPE '|'
query result set

Conclusion

The LIKE operator is frequently using in SQL SERVER, but sometimes working with it can be tricky. Understanding the LIKE patterns including wildcard characters will allow you to use this operator more confidently.

Next Steps

Useful information related to this article can be found below:

Also, check out this tutorial for more examples of using the LIKE operator.




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips


Article Last Updated: 2021-02-02

Comments For This Article




Sunday, October 3, 2021 - 2:36:57 AM - Bilal Arar Back To Top (89298)
Very good... Thank you

Tuesday, August 31, 2021 - 7:36:32 AM - Andyhsu Back To Top (89197)
Very good knowledge to me. Thanks

Monday, September 17, 2018 - 2:24:35 AM - Göran Peterson Back To Top (77610)

 Really good stuff, some new things here for me. Great!

 



download














get free sql tips
agree to terms