Why do I need to run twice (use 4 \) to find the backslash (\) in pure SQL?

Question

I do not understand this MySQL behavior: if I want to display \ b, I can simply select "a\\b" , which work without problems:

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

But if I search for a row containing table \ in the table using LIKE, I need to escape from "\" twice. Why?

Here is an example.

We will 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 are trying to get entries starting with "a \ b" ...

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

Why is \\ simply ignored? Why do I need to double basckslash to get the 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

First you execute the string syntax, then for the LIKE syntax.

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

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

So, in both syntaxes you need to exit \ .

---

If you do not want to use \ to avoid the LIKE pattern, you can use the ESCAPE keyword. For instance:

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

So you need to write |% , |_ or || to avoid these special characters.

Answer 2

See String Comparison Functions

The like operator is compared to a pattern, which may include % and _ . To avoid this, you should use \ . Thus, the backslash is also a special character.

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