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.

Answer 1

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

Answer 2

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