PDA

View Full Version : How to query and replace text in posts


TundraSoul
11-24-2014, 10:54 PM
I'd like to replace some text in the vB4.x post table but phpmyadmin won't do it when I use the %.

See my example:

UPDATE `admin_mydatabase`.`post` SET `pagetext` = REPLACE(`pagetext`, 'https://vborg.vbsupport.ru/', 'http://wwww.mysite.com/images/not-available.gif') WHERE `pagetext` LIKE '%https://vborg.vbsupport.ru/%' COLLATE utf8mb4_bin

The query runs successfully but it doesn't replace the text. I suspect it doesn't understand that I'd like it to replace all the image names in the post table regardless of their file names.

How can I make this work? Thanks for your help.

kh99
11-24-2014, 11:18 PM
I don't think you can use wildcards in REPLACE(). I'm not an SQL expert, but as far as I know there's no way to do a replacement like that in mySQL. You'd need to do a SELECT query, then do the replacement in php and update the row. You can use WHERE pagetext LIKE and use % in that pattern to select only the posts with images.

Also, the posts are cached, so to see the changes you'd need to delete the corresponding cached posts from the postparsed table, or if this is a one-time thing then you could just truncate that table when you're done the updates.