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

MysqlEscapingSpecial Characters

Mysql Problem Overview

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'

Mysql Solutions

Solution 1 - Mysql

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 '|';
Categories
Recommended Mysql Solutions
Recommended Escaping Solutions
Recommended Special Characters Solutions

Previous Solution:

Why is default required for a switch on an enum?

JavaEnumsDefaultSwitch Statement

Next Solution:

MATLAB - How to zoom subplots together?

MatlabPlotZooming

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionKipView Question on Stackoverflow
Solution 1 - MysqlajrealView Answer on Stackoverflow