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.
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.
Your query works fine for me in MySQL 5.5.8:
mysql> create table exampletable (
title varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.15 sec)
mysql>
mysql> insert into exampletable values ('Old King\\''s Road'),
('Running Down A Dream'),('Can\\''t Stop The Sun'),('This One''s For Me');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from exampletable WHERE title LIKE '%\\\\%';
+---------------------+
| title |
+---------------------+
| Old King\'s Road |
| Can\'t Stop The Sun |
+---------------------+
2 rows in set (0.01 sec)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With