MySQL Bugs: #3688: Problems with an escaped backslash

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

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'

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'.

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

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)."