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