CODEWITHSUNDEEP
mysql
Rocco The Taco
Rocco The Taco

Reputation: 3777

Double Quotes in MySQL UPDATE Query

I'm using the following answer to make changes to a text field in multiple records in a MySQL table.

I need to change a small portion of each record where a URL occurs with double quotes.

Example record:

a:32:{s:5:"title";s:23:"Texas"footertext";s:220:"<a class=\"footerlink\" href=\"%ORIGINALPOSTURL%\" target=\"_blank\" rel=\"nofollow\">CLICK HERE FOR MORE INFO &amp; PHOTOS</a>}

I want to remove the target=\"_blank\"

I'm not updating any records I suspect due to the use of double quotes in the target value I'm trying to replace. I've tried with and without backslashes. These 4 backslashes on each side of the double quotes you see here worked when performing a SELECT in phpMyAdmin. I've tried a single backslash with zero records updated too.

Neither of these worked

UPDATE wp_autoblog SET feed_meta = replace(feed_meta,'target=\\\\"_blank\\\\"','');

UPDATE wp_autoblog SET feed_meta = replace(feed_meta,'target=\"_blank\"','');

Upvotes: 2

Views: 2858

Answers (1)

Rocco The Taco
Rocco The Taco

Reputation: 3777

It turns out phpMyAdmin inserts an extra backslash when displaying the data in a web browser.

I ended up doing a .sql dump of the table and the data I'm trying to replace is wrapped like this: target=\\\"_blank\\\"

I issued the followed command and it worked great. 

UPDATE wp_autoblog SET feed_meta = REPLACE( feed_meta,  'target=\\\"_blank\\\"',  '' )

Upvotes: 2

Related Questions