How to escape literal percent sign when NO_BACKSLASH_ESCAPES option is enabled?

mysql escaping special-characters
Kip picture Kip · Feb 16, 2011 · Viewed 34.1k times · Source

My company runs MySQL in NO_BACKSLASH_ESCAPES mode. How can I escape a literal % or _ in a LIKE query in this mode? The standard way is \%, but that doesn't work in this mode.

Example: a column has the following values: 5% off, 50% off. The following query works in standard mode but not in NO_BACKSLASH_ESCAPES mode:

SELECT * FROM mytable
WHERE mycol LIKE '5\% off'

Answer

ajreal picture ajreal · Feb 16, 2011

you need escape

select * from mytable
where mycol like '5\% off' escape '\';

For a version that works regardless of NO_BACKSLASH_ESCAPES mode, you can use a different character, like pipe:

select * from mytable
where mycol like '5|% off' escape '|';

Related questions

How to escape apostrophe (') in MySql?

The MySQL documentation says that it should be \'. However, both scite and mysql shows that '' works. I saw that and it works. What should I do?

Read More
Escaping single quote in PHP when inserting into MySQL

I have a perplexing issue that I can't seem to comprehend... I have two SQL statements: The first enters information from a form into the database. The second takes data from the database entered above, sends an email, and then …

Read More
How do I escape reserved words used as column names? MySQL/Create Table

I am generating tables from classes in .NET and one problem is a class may have a field name key which is a reserved MySQL keyword. How do I escape it in a create table statement? (Note: The other problem …

Read More