CODEWITHSUNDEEP
mysqlbackslash
8bitsdeep
8bitsdeep

Reputation: 61

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.

Upvotes: 6

Views: 9021

Answers (4)

mitsu
mitsu

Reputation: 453

In the case of Ruby on Rails

search_value.gsub!(/\\/, "\\\\\\\\\\")
MyModel.where(["column_name ?","%#{search_value}%"])

If you don't want to use a wild card, add them.

search_value.gsub!(/%/, "\\%").gsub!(/_/, "\\_")

Upvotes: 0

compilingEntropy
compilingEntropy

Reputation: 51

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.

Upvotes: 5

Ike Walker
Ike Walker

Reputation: 65547

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)

Upvotes: 2

Raisen
Raisen

Reputation: 4495

Try:

SELECT title FROM exampletable WHERE title LIKE '%\\%'

Upvotes: 0

Related Questions