Menu
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' 
801
You need to escape it: From the tests I did on SQLFiddle . By default the \ is already the ESCAPE , such that just doing: SELECT * FROM tb_table WHERE nome_fantasia LIKE 'name\%name';
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.
The MySQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
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 '|'; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
