MySQL LIKE operator with wildcard and backslash

Question

It's frustrated with MySQL's pattern escaping used in LIKE operator.

root@dev> create table foo(name varchar(255));
Query OK, 0 rows affected (0.02 sec)

root@dev> insert into foo values('with\\slash');
Query OK, 1 row affected (0.00 sec)

root@dev> insert into foo values('\\slash');
Query OK, 1 row affected (0.00 sec)

root@dev> select * from foo where name like '%\\\\%';
Empty set (0.01 sec)

root@dev> select * from foo;
+------------+
| name       |
+------------+
| with\slash | 
| \slash     | 
+------------+
2 rows in set (0.00 sec)

root@dev> select * from foo where name like '%\\\\%';
Empty set (0.00 sec)

root@dev> select * from foo where name like binary '%\\\\%';
+------------+
| name       |
+------------+
| with\slash | 
| \slash     | 
+------------+
2 rows in set (0.00 sec)

According to MySQL docs: http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like %\\\\% is the right operand, but why it yields no result?

EDIT: The database I'm testing that in has character_set_database set to utf8. To further my investigation, I created the same setup in a database that has character_set_database set to latin1, and guess what, '%\\\\%' works!

EDIT: The problem can be reproduced and it's the field collation problem. Details: http://bugs.mysql.com/bug.php?id=63829

When I use your commands exactly, `select * from foo where name like '%\\\\%';` works for me. I'm at a loss as to why it doesn't work for you though, I'm curious to know. — mathematical.coffee, Dec 20 '11 at 23:32
It may have something to do with the database charset. I've updated the original post. — EnToutCas, Dec 21 '11 at 03:24
Check this :- `select @@session.sql_mode; select @@global.sql_mode;` — ajreal, Dec 21 '11 at 03:42
I also tried creating a new db with `character_set_database` being utf8 and `'%\\\\%'` still worked for me (I probably screwed up setting it to utf8 properly though). What about changing the escape character? Like `SELECT * from foo where name like '%|\%' ESCAPE '|'`, or maybe `%|\\%` (sorry, I can't try it out since I can't reproduce the problem). I'm most curious as to the answer though! This person also asked the same question, no definitive answers but possibly a few hints: http://stackoverflow.com/questions/5625698/need-to-select-only-data-that-contains-backslashes-in-mysql — mathematical.coffee, Dec 21 '11 at 03:45
The table that doesn't work has: CHARSET=utf8 COLLATE=utf8_unicode_ci, I don't know if collation could be the problem here. Thanks anyway. — EnToutCas, Dec 21 '11 at 04:03

score 2 · Answer 1 · answered Jan 05 '17 at 20:14

In MySQL 5.6.10, with the text field collation utf8mb4_unicode_520_ci this can be achieved by using 5 backslash characters instead of 4, i.e:

select * from foo where name like binary '%\\\\\%';

Somehow, against all expectations, this properly finds all rows with backslashes. At least this should work until the MySQL field collation bug above is fixed. Considering it's been more than 5 years since the bug is discovered, any app designed with this may outlive its usefulness before MySQL is even fixed - so should be a pretty reliable workaround.

score 1 · Answer 2 · edited Aug 07 '17 at 18:29

1

With MySQL 5.0.12 dev on Windows 10 I got the following results when I changed the query from

SELECT * FROM `foo` WHERE `name` LIKE '%http:\/\/%'

to

SELECT * FROM `foo` WHERE `name` LIKE '%http:\\\\\\\%'

it works and yet the first string with forward slashes was the original field content. It seems to have interpreted forward slashes as backslashes.

edited Aug 07 '17 at 18:29

Max Play

3,717
1
22
39

answered Aug 07 '17 at 13:08

Darren Bailey

11
1

newtover · Answer 3 · 2011-12-21T08:18:19.473

It seems it has some relation to that MySQL bug: http://bugs.mysql.com/bug.php?id=46659

I think you connect to mysql not specifying correct --character-set-server option (which defaults to latin1 with collation latin1_swedish_ci), and having utf-8 as the current charset of the console. That causes incorrect char conversions and comparisons when you deal with data which supposed to be converted to the utf8 from the charset of --character-set-server.

MySQL LIKE operator with wildcard and backslash

3 Answers3

Linked