T-SQL special characters to escape for LIKE operator wildcard search

Question

SQL Server has the LIKE operator to handle wildcard searches. My customer wants to use the "*" (asterisk) character in the user interface of an application as the wildcard character. I'm just wondering if there are any standard characters that I need to worry about (that are used as special characters in SQL Server) besides the "%" (percent) character itself before performing a LIKE wilcard search in case their keyword contains a "%" and needs to find a "%" in the actual string. If so, what are they?

So please assume that [table1].[column1] will never have a "*" (asterisk) in the text string!

Here's what I have so far. Do I need to handle more situations other than the standard "%" character and the custom "*"

-- custom replacement
select REPLACE('xxx*xxx', '*', '%')

-- standard replacements
select REPLACE('xxx%xxx', '%', '[%]')
select REPLACE('xxx_xxx', '_', '[_]')  -- ???
select REPLACE('xxx[xxx', '[', '[[]')  -- ???
select REPLACE('xxx]xxx', ']', '[]]')  -- ???

Example:

SET @p = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@p, ']', '[]]'), '[', '[[]'), '_', '[_]'), '%', '[%]'), '*', '%')

SELECT 'xxxxxxxxx%xxxxxx' LIKE @p

SELECT [table1].[column1] LIKE @p

score 10 · Accepted Answer · answered Oct 23 '13 at 21:01

10

It looks like you got them all, although I think escaping ']' is unnecessary. Technically you should just need to escape the opening bracket ('[').

DECLARE @Table1 TABLE
(
   Column1 VARCHAR(32) NOT NULL PRIMARY KEY
);

INSERT @Table1(Column1)
VALUES
   ('abc%def'),
   ('abc_def'),
   ('abc[d]ef'),
   ('abc def'),
   ('abcdef');

DECLARE @p VARCHAR(32) = 'abc*]*';

DECLARE @Escaped VARCHAR(64) = REPLACE(@p, '[', '[[]');
SET @Escaped = REPLACE(@Escaped, '_', '[_]');
SET @Escaped = REPLACE(@Escaped, '%', '[%]');
SET @Escaped = REPLACE(@Escaped, '*', '%');

SELECT T.Column1
FROM @Table1 T
WHERE T.Column1 LIKE @Escaped;

answered Oct 23 '13 at 21:01

John Keller

596
5
4

Why don't I need the closing bracket? Because we don't want the LIKE operator to take that character and interpret it as a specifier? – JustBeingHelpful Oct 23 '13 at 21:53
1

@MacGyver, please reference the section **Using Wildcard Characters As Literals** at http://msdn.microsoft.com/en-us/library/ms179859(v=sql.105).aspx. You'll see that ']' does not need escaping. The parsing must initially look for open bracket ([) first and then the following closing bracket (]). – John Keller Oct 24 '13 at 13:37
thanks for pointing out that I do not need the ] (closing bracket). I left that alone and it worked fine. – JustBeingHelpful Oct 24 '13 at 20:47

score 2 · Answer 2 · edited May 23 '17 at 12:14

2

Looks good!! - Have a look here for all the information related to the LIKE clause.

Also List of special characters for SQL LIKE clause

edited May 23 '17 at 12:14

Community

1
1

answered Oct 23 '13 at 21:01

John Bustos

19,036
17
89
151

John - this questions lists a single quote, but I don't think you need to do anything special for the LIKE clause with a single quote. I think that has a different context of "special" meaning when you type a single quote into SQL Server Management Studio, you need to use two of them to mean one. Right? I'm really looking for the "perfect" command to meet my use case described. – JustBeingHelpful Oct 23 '13 at 21:12
1

@MacGyver, I agree with you... I really think you've got them all! - I would do a quick test to make sure your SQL runs with `'` in your query string, but my guess is it will.... – John Bustos Oct 23 '13 at 21:17
Cool. I haven't used the specifier or the escape clause (from your link) syntax ever. If (theoretically) an end-user who was smart enough, typed one of those in, do you think this would still hold water? I'm trying to test those out right now..... because I am just escaping the first and last bracket, all of the STUFF in between will be part of their keyword. So I'm just looking for your advice in how a good interface would handle those. Any ideas? – JustBeingHelpful Oct 23 '13 at 21:33
I think that since you're only creating the `LIKE` statement, you don't have to worry about the ESCAPE clause since that is not part of the like pattern. – John Bustos Oct 24 '13 at 15:02
Truly, it seems you've taken care of everything with your statement... Aside from testing the `'`, I think you're good to go!!! – John Bustos Oct 24 '13 at 15:21

T-SQL special characters to escape for LIKE operator wildcard search

2 Answers2

Linked