52

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'
Kip
  • 107,154
  • 87
  • 232
  • 265

1 Answers1

71

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 '|';
Kip
  • 107,154
  • 87
  • 232
  • 265
ajreal
  • 46,720
  • 11
  • 89
  • 119
  • 1
    thanks. there need to be single-quotes around the backslash, not double-quotes. – Kip Feb 16 '11 at 18:09
  • oh, is the same since escape is being escaped in NO_BLACKSLASH_ESCAPES – ajreal Feb 16 '11 at 18:10
  • @ajreal did you mean `NO_BACKSLASH_ESCAPES`? – Cacovsky Jun 05 '13 at 20:58
  • The backslash needs to be escaped as well, I took the liberty of updating your answer. – BenMorel Oct 25 '13 at 14:26
  • @Benjamin actually your edit introduces a syntax error. the question is specifically asking about MySQL in NO_BACKSLASH_ESCAPES mode. In that mode, backslash is not the escape character – Kip Nov 04 '13 at 13:18