SQL Server has a great feature for finding rows with special characters. You can use the “column name (text) with a special character” function to find rows with that text. The function takes two arguments: the column name and the text you want to search for. Here’s an example: SELECT column_name(text) WITH A SPECIAL CHARACTER FROM INFORMATION_SCHEMA; The first argument is the column name, and the second is the text you want to search for. The result will be returned in a table row.
So yes, this post is solely for my benefit. Hopefully it will help somebody else too.
Let’s say you want to find any fields that contain the text “100%”, so you put together this query:
Instead of what you wanted, you’ll get all the rows that contain “100” as well as the rows that contain “100%”.
The problem here is that SQL Server uses the percent sign, underscore, and square brackets as special characters. You simply can’t use them as a plain character in a LIKE query without escaping them.
Square Bracket Escape
You can surround the % or _ with square brackets to tell SQL Server that the character inside is a regular character.
T-SQL ESCAPE Syntax
Alternatively, you can append the ESCAPE operator onto your query, and add a \ character before the value you want to escape.
The ESCAPE ‘\’ part of the query tells the SQL engine to interpret the character after the \ as a literal character instead of as a wildcard.
Personally I find the second method easier to deal with, and you can use it to escape a square bracket as well.
