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

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

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";
Console.WriteLine(text); // output: abcd\efg

text = @"abcd\\efg";
Console.WriteLine(text); // output: 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 they 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 possibly 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 procedure to work without relying on the data being passed in being cleaned.

Solution

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