Need to select only data that contains backslashes in MySQL
I'm attempting to correct entries in a database that have been double-escaped. I believe magic_quotes was on while mysql_real_escape_string was being used. The double-escaping has caused some search results to be incorrect/missing.
I've turned magic_quotes off and am planning to search for entries with a backslash and update them to remove any double-escaping. Trouble is, when I perform a query to search for entries with backslashes, I always get no results.
SELECT title FROM exampletable WHERE title LIKE '%\\\\%'
I'm using '%\\\\%' as recommended here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like
If I output every title, many of them have unwanted backslashes, so I know they're there. I just can't seem to isolate them in a query.
Example Data:
Old King\'s Road
Running Down A Dream
Can\'t Stop The Sun
This One's For Me
Again, just trying to return entries with a \ in them.
EDIT: MySQL version is 5.0.92-community. Collation is latin1_swedish_ci. Charset is UTF-8 Unicode
%\\% does NOT work. I've tried. It is listed as incorrect on mysql.com:
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.
Answer
This command works for me when I'm running the command with php:
select * from exampletable where title like '%\\\\\\\\%' or title like '\\\\\\\\%' or title like '%\\\\';
The reason I include the beginning, anywhere, and end matches is to show that they are different. If you're looking for backslashes on the end, there only needs to be 4 backslashes. this is because there is nothing to escape after it. You use 8 backslashes on the other commands because they get parsed by php.