How do I escape a percentage sign in T-SQL?

Question

This question also has the answer but the mentions DB2 specifically.

How do I search for a string using LIKE that already has a percent % symbol in it? The LIKE operator uses % symbols to signify wildcards.

Answer 1

WHERE column_name LIKE '%save 50[%] off!%'

Answer 2

You can use the ESCAPE keyword with LIKE. Simply prepend the desired character (e.g. '!') to each of the existing % signs in the string and then add ESCAPE '!' (or your character of choice) to the end of the query.

For example:

SELECT *
FROM prices
WHERE discount LIKE '%80!% off%'
ESCAPE '!'

This will make the database treat 80% as an actual part of the string to search for and not 80(wildcard).

MSDN Docs for LIKE

Answer 3

Use brackets. So to look for 75%

WHERE MyCol LIKE '%75[%]%'

This is simpler than ESCAPE and common to most RDBMS