How to escape a string for use with the LIKE operator in SQL Server?

Question

I am looking for something that works in SQL Server similar to the @ symbol in c# which causes a string to be taken as it's literal. Eg:

string text = "abcd\\efg";
Output of text = abcd\efg

string text = @"abcd\\efg";
Output of text = abcd\\efg

Note how the @ affected the string to take every character as is.

Now I am not sure this is possible but here is my issue and maybe there is a better way to solve this. Consider the following basic query:

SELECT [Name] 
  FROM [Test] 
 WHERE [Name] LIKE (@searchText + '%')

My issue is if they put a %, _ or any other of those special characters that can affect my like clause. I want the match to act just like a 'starts with' function. So is there anything I can apply to the @searchText to say take this literally or is there possbibly a better solution that I am not thinking of?

Edit: I do not want the solution to be client side cleaning. I need this stored proc to work without relying on the data being passed in being cleaned.

To avoid confusion, the issue here is escaping a string for use with the LIKE operator. — recursive, Sep 15 '09 at 16:34
See also: http://stackoverflow.com/questions/439495/tsql-like-escape-clause — recursive, Sep 15 '09 at 16:40

score 27 · Accepted Answer · edited Jul 26 '12 at 14:20

27

To search for "%" as a literal not wildcard in a string, it needs escaped as [%].

Now, SQL Server only need 3 characters escaping: % _ [

So, create a scalar udf to wrap this:

REPLACE(REPLACE(REPLACE(@myString, '[', '[[]'), '_', '[_]'), '%', '[%]')

Because of the simplicity (aka: very limited) pattern matching in SQL, nothing more complex is needed...

edited Jul 26 '12 at 14:20

Jax

6,860
4
27
38

answered Sep 15 '09 at 17:21

gbn

422,506
82
585
676

Neil N · Answer 2 · 2009-09-15T16:51:39.463

9

In TSQL, you can wrap the % and _ characters in brackets like so [%] [_] this tells SQL to treat them as literals.

I have tested and verified this works in SQL Server 7.0, 2000, and 2005.

http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx

edited Sep 15 '09 at 16:51

answered Sep 15 '09 at 16:34

Neil N

24,862
16
85
145

So doing "...where columnName LIKE '[%]foo%'" would actually search for any value that starts with "%foo" (the % acting as a literal, not as a wildcard? I was not aware of that. – Adam Robinson Sep 15 '09 at 16:36
This isn't true for string literals. – David Sep 15 '09 at 16:37
1

Ah, I stand corrected. I've never seen this used in 10 years. :-P – David Sep 15 '09 at 16:45
I've been doing SQL for 10 years too, and just YESTERDAY learned this because I couldn't get like statements to work with underscores. So I read the docs and saw the light. – Neil N Sep 15 '09 at 16:50
So what happens if my string has a [ in it already like: ab[%]cd and I want it to actually match exactly? – Kelsey Sep 15 '09 at 17:05
Kelsey, read the docs, they explain how to escape brackets as well. – Neil N Sep 15 '09 at 17:10
@Kelsey - then you can define an escape charater using LIKE @text ESCPAPE '~' and your text would be ab~[~%~]cd. See my updated/corrected answer below. – David Glenn Sep 15 '09 at 17:17
+1 for the details but I have to award the answer to gbn as he included code that I could just cut and past and add. I would like to have given you a +2 though :) – Kelsey Sep 15 '09 at 17:37

score 0 · Answer 3 · answered Sep 15 '09 at 16:29

0

Each character to be treated literally should be enclosed in square brackets. A right bracket is taken literally directly so don't enclose that one.

answered Sep 15 '09 at 16:29

Joshua

40,822
8
72
132

David Glenn · Answer 4 · 2009-09-15T17:03:45.023

0

If you parameterize your query you don't need to worry about it.

UPDATE

As recursive stated in the comments, % still needs to be escaped even in parameterized queries, I didn't realize linq to sql was doing it automagically when I tested.

You can use ESCAPE 'x' where x is the character you wish to be the escape character. Linq to SQL does it like this

WHERE [Name] LIKE @searchText ESCAPE '~'

where @searchText = [some text with a~% character%]

or as others have stated it can be escaped with [%]

view the documentation

edited Sep 15 '09 at 17:03

answered Sep 15 '09 at 16:42

David Glenn

24,412
19
74
94

This is not true. Try it yourself. – recursive Sep 15 '09 at 16:48
The question in the OP is unrelated to Parameterized queries. The question is valid with or without params – Neil N Sep 15 '09 at 16:52
I wasn't aware of that. Linq to SQL does it automatically. I've corrected my answer. – David Glenn Sep 15 '09 at 17:06

score -1 · Answer 5 · answered Sep 15 '09 at 16:31

-1

I'd sanitize the string in the front-end application rather than try and do hokey stuff in SQL to work around this.

answered Sep 15 '09 at 16:31

Wyatt Barnett

15,573
3
34
53

1

What if you ACTUALLY need percent signs or underscores in text? – Neil N Sep 15 '09 at 16:39
3

I don't want my stored proc to rely on the caller passing it cleaned data. – Kelsey Sep 15 '09 at 17:07

David · Answer 6 · 2009-09-15T16:49:21.620

-1

If you don't want to modify the incoming text, you can use the "LEFT" function to create your own "STARTSWITH":

SELECT [Name] 
  FROM [Test] 
 WHERE @searchText = LEFT( [Name], LEN( @searchText ) )

(Note that you probably need to do extra work to handle the case of NULLs or empty string.)

EDIT: Removed the incorrect statement about using "LIKE" to search for "%".

edited Sep 15 '09 at 16:49

answered Sep 15 '09 at 16:39

David

34,223
3
62
80

It appears to be very common misconception, but you CAN escape % and _ in LIKE comparisons – Neil N Sep 15 '09 at 16:41

score -1 · Answer 7 · answered Sep 15 '09 at 16:44

-1

From the docs:

Syntax

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Use the ESCAPE option like so:

SELECT [Name] 
  FROM [Test] 
 WHERE [Name] LIKE (REPLACE(@searchText, '%', '%%') + '%') ESCAPE '%'

answered Sep 15 '09 at 16:44

recursive

83,943
34
151
241

I thought of this route but would I need to do this for %, _, [, ], and any other characters? That seems like a lot of searching and replacing. – Kelsey Sep 15 '09 at 17:06

How to escape a string for use with the LIKE operator in SQL Server?

7 Answers7

Linked