Bug #3688 Problems with an escaped backslash
Submitted: 7 May 2004 20:28 Modified: 7 May 2004 20:45
Reporter: Jan Christian Herlitz Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.16 OS:Windows (Windows XP)
Assigned to: Dean Ellis CPU Architecture:Any

[7 May 2004 20:28] Jan Christian Herlitz
Description:
I have the string '\a' in a TEXT column after this query.

 UPDATE mytable SET col='\\a' WHERE index=1

MySQL does NOT find the string with the following query:

 SELECT * FROM mytable WHERE col LIKE '\\a'

How to repeat:

 UPDATE mytable SET col='\\a' WHERE index=1
 SELECT * FROM mytable WHERE col LIKE '\\a'
[7 May 2004 20:45] Dean Ellis
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See: http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html

You would have to use LIKE '\\\\a'.
[10 May 2004 11:42] Jan Christian Herlitz
Thank you - MySQL now finds the string with: LIKE '\\\\a'

I still don't see why I have to escape '\' twice.

With Oracle the SQL looks like:

 SELECT * FROM strings WHERE col LIKE '\\a' ESCAPE '\'

In MySQL:

 SELECT * FROM strings WHERE col LIKE '\\\\a' ESCAPE '\\'

In the documentation it says:

 Within a string, certain sequences have special meaning.
  \%   A '%' character.
  \_   A '_' character.
  \\   A backslash ('\') character. 

How do I know from this that backslash must be escaped once more?

/Jan Christian
[24 May 2004 18:18] Dean Ellis
From the manual chapter above, regarding LIKE:

"Note: Because MySQL uses the C escape syntax in strings (for example, `\n' to represent newline), you must double any `\' that you use in your LIKE strings. For example, to search for `\n', specify it as `\\n'. To search for `\', specify it as `\\\\' (the backslashes are stripped once by the parser and another time when the pattern match is done, leaving a single backslash to be matched)."