Bug #55449 multi-backslashes in "LIKE" clause not work and WHERE... LIKE is case-sensitive
Submitted: 21 Jul 2010 16:40 Modified: 21 Jul 2010 20:59
Reporter: Zhangling Ren Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version: 5.0.86-enterprise-nt OS:Windows (2008)
Assigned to: CPU Architecture:Any
Tags: LIKE clause, UPDATE

[21 Jul 2010 16:40] Zhangling Ren
I’m writing a script to update system host names in our database. The host names are represented in URL, UNC and other forms in the database.  
Example data:
Column A, B and C contain system host name data, where column A, B and C have VARCHAR data type and might or might not be in the same table. 
Column A might have following data: 
·	Record 1: \\Xyz_ABC\FOLDER_X\FOLDER_Y\... 
·	Record 2: \\Xyz_AbcD\folder_Z\FOLDER_B\...
·	Record 3: \\Bxyz_ABC\folder_X\FOLDER_Y\... 
·	Record 4: \\Bxyz_abcd\FOLDER_Z\FOLDER_Y\... 
·	Record 5: \\xyz_abc\FOLDER_X\FOLDER_Y\... 
Column B might have following data: 
·	Record 1: bxyz_abc:1
·	Record 2: xyz_abc:2
·	Record 3: xyz_abcd:3 
·	Record 4: Xyz_ABC:1
Column C might have following data: 
·	Record 1: http://xyz_ABC:8080/folder_A/folder_B
·	Record 2 http://xyz_AbcD/folder_a/folder_B
·	Record 3: http://bxyz_ABC:8080/folder_A/folder_B
·	Record 4: http://bxyz_abcd/folder_a/folder_B
·	Record 5: http://xyz_Abc:8080/folder_A/folder_B
Need to:
Update only the records with system name of “xyz_abc” (regardless of upper-case or lower-case of letters) to a new host name. 

I found out that UPDATE query with WHERE...LIKE clause is case-sensitive and I couldn't update all relevant records in one UPDATE query if the letter cases are different. 

I tried to include “\” in LIKE clause to (uniquely) select the portion including “\” (e.g. “\Xyz_ABC\” or “\\Xyz_ABC\”). However, I couldn’t make it work after I tried with various combinations of escape characters (followed online documents).

How to repeat:
1. UPDATE a column that contains mixed upper/lower case of letters using a "WHERE...LIKE" clause with only upper or lower case of letters. 

2. UPDATE a column that contains multiple backslashes (e.g. UNC path) using a "WHERE...LIKE" clause that includes more than one backslashes.

Suggested fix:
1. should be case-insensitive for WHERE... LIKE... clause
2. should correctly parse several blackslashes in "like" clause
[21 Jul 2010 20:59] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like starting from:


Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.
[28 Jul 2010 1:27] MySQL Verification Team
Duplicate of bug #39808