Escape SQL LIKE wildcard characters

#sql #csharp

In MSSQL, LIKE operator has wildcard characters that aids in pattern matching.

If you want to disable it, one way is to manually escape it. In my case, I made a string extension to transform the search text before passing it to the stored procedure via ADO.

SQL recognizes which character to escape if it was enclosed in [] with the exemption of single quote '.

public static string EscapeSqlChars(this string input)
{
    return input
        .Replace(@"[", @"[[]")
        .Replace(@"\", @"[\]")
        .Replace(@"'", @"''")
        .Replace(@"%", @"[%]")
        .Replace(@"_", @"[_]");
}