I have user input coming in a parameter which then goes into a query to do a wildcard search.

Similar to:

DECLARE @myVariableContainingUserInput VARCHAR(50) = 'part of string';

DECLARE @myTable TABLE (column1 varchar(200));

INSERT INTO @myTable(column1) 
VALUES ('This is the whole string that contains part of string the user is looking for')

SELECT column1 
FROM @myTable 
WHERE column1 LIKE '%' + @myVariableContainingUserInput + '%'

In which case they get the one row.

However some times the data looks like this:

INSERT INTO @myTable(column1) 
VALUES ('This is the whole string that contains [part] of string the user is looking for')

and the user sometimes might search for

SET @myVariableContainingUserInput  = '[part';

and they would expect that to find one row containing 'This is the whole string that contains [part] of string the user is looking for'. It doesn't because [ is a special character.

I know I could escape it by using [[] or by for example adding a \ and using ESCAPE '\' if I was the one in control of the search values. The input is coming from a .NET application and I have no way of knowing which special characters are coming in the parameter ahead of time or where they are in the string or how many they may be.

Is there a standard function or correct way of escaping characters going into a like comparison that would work in SQL Server 2008 (or a .NET function that could be used prior to the SQL statement hitting the server)? Or is my best bet to just create a list of special characters and loop though and escape them or use a regex to do the same?

I just know that doing a diy version would likely be to have more issues than without extensive testing than a tried and tested version.

I have tried looping through a list of special characters and replacing them with [[] or by for example adding a \ and using ESCAPE ''

But nothing that works well for different mixtures of characters coming in in different quantities or orders. The sql server 2008 in in the question because it is the minimum support version for the clients.