SQL LIKE operator ESCAPE statement in the LIKE-query
Example
If you implement a text-search as LIKE-query, you usually do it like this:
SELECT *
FROM T_Whatever
WHERE SomeField LIKE CONCAT('%', @in_SearchText, '%')
However, (apart from the fact that you shouldn't necessarely use LIKE when you can use fulltext-search) this creates a problem when somebody inputs text like "50%" or "a_b".
So (instead of switching to fulltext-search), you can solve that problem using the LIKE-escape statement:
SELECT *
FROM T_Whatever
WHERE SomeField LIKE CONCAT('%', @in_SearchText, '%') ESCAPE '\'
That means \ will now be treated as ESCAPE character.
This means, you can now just prepend \ to every character in the string you search, and the results will start to be correct, even when the user enters a special character like % or _.
e.g.
string stringToSearch = "abc_def 50%";
string newString = "";
foreach(char c in stringToSearch)
newString += @"\" + c;
sqlCmd.Parameters.Add("@in_SearchText", newString);
// instead of sqlCmd.Parameters.Add("@in_SearchText", stringToSearch);
Note:
The above algorithm is for demonstration purposes only.
It will not work in cases where 1 grapheme consists out of several characters (utf-8).
e.g. string stringToSearch = "Les Mise\u0301rables";
You'll need to do this for each grapheme, not for each character.
You should not use the above algorithm if you're dealing with Asian/East-Asian/South-Asian languages. Or rather, if you want correct code to begin with, you should just do that for each graphemeCluster.
See also ReverseString, a C# interview-question
