Today I had cause to search a text field in a SQL Server 2005 database for the phrase %3 (I had some data being url encoded incorrectly) and this wasn't something I'd tried to do before so I had to find out how to escape the % sign. A quick google and a re-read of the LIKE syntax, and I discovered that to escape the % I had to do something like
WHERE [Value] LIKE '%!%3%' ESCAPE '!'
After talking this through with a colleague, he asked me to try some other escape characters, and here's what we found out:
Attempting to use a string as an escape phrase, i.e.
WHERE [Value] like '%WORD%1%' ESCAPE 'WORD'
doesn't work, and fails with an error of
Msg 506, Level 16, State 1, Line 1
The invalid escape character "WORD" was specified in a LIKE predicate.
Attempting to use % as the escape character, gets it quite confused
WHERE [Value] like '%%%1%' ESCAPE '%'
returns 0 rows, but no errors
Attempting to use ' as an escape character, which needs to be escaped itself
WHERE [Value] like '%''%1%' ESCAPE ''''
returns the correct rows