Why I need to double-escape (use 4 \) to find a backslash ( \ ) in pure SQL?

Question

I do not understand this MySQL behaviour : if I want to display a\b, I can just select "a\\b" which work without problem :

mysql> select "a\\b";
+-----+
| a\b |
+-----+
| a\b |
+-----+
1 row in set (0.05 sec)

But if I wnat to search a string containing a \ in a table using LIKE, I need to double-escape my "\". Why ?

Here is an example.

We prepare a small table.

create table test ( test varchar(255) );
insert into test values ( "a\\b" ) , ( "a\\b\\c" ) , ( "abcd" );

mysql> select * from test;
+-------+
| test  |
+-------+
| a\b   |
| a\b\c |
| abcd  |
+-------+
3 rows in set (0.05 sec)

We try to get entries beginning by "a\b" ...

mysql> select * from test where test LIKE "a\\b%";
+------+
| test |
+------+
| abcd |
+------+
1 row in set (0.05 sec)

Why \\ is just ignored there? Why I need to double-escape basckslash to get my expected result?

mysql> select * from test where test LIKE "a\\\\b%";
+-------+
| test  |
+-------+
| a\b   |
| a\b\c |
+-------+
2 rows in set (0.04 sec)

Answer 1

See String Comparison Functions

The like operator compares against a pattern, which might include % and _. To escape these, you must use \. So backslash is a special character, too.

When you enter the pattern string "a\\\\b" it is interpreted by Mysql and then again by the like operator, wich gives "a\\b" and then "a\b".

Answer 2

You escape first for the string syntax, then for LIKE syntax.

In LIKE characters % and _ have special meaning, so if you want to search for literal %, you need to use \%, and if you want to search for literal \% you need to escape the backslash as in \\%.

In string syntax " obviously has special meaning, so if you want to include quote in the string you need to escape it as \", and to include literal \" in the string you have to escape the backslash as in \\".

So in both syntaxes you have to escape \.

---

If you don't want to use \ to escape the LIKE pattern , you can use ESCAPE keyword. For example:

...  where test LIKE "a\\b%" ESCAPE '|';

This way, you'll need to write |%, |_ or || to escape these special chars.