Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Examples and Function for Using SQL Server LIKE Operator and Wildcard Characters


By:   |   Last Updated: 2018-09-10   |   Comments (1)   |   Related Tips: More > T-SQL

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 with a pattern containing wildcard characters along with a function you can use to make this easier.

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

Create Sample Data to Test SQL Server LIKE Operator

Let's start by creating a test environment:

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 Example

Let's assume that we want to find all logins that start with '_' symbol. Try to predict the results of the following query before executing it:

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 operator, 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, 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 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 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

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:

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.



Last Updated: 2018-09-10


next webcast button


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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

 


Learn more about SQL Server tools