0

i'm trying to:

1- Find a string inside a lot of content

2- Replace that specific part of the string with a new one using REPLACE function like this:

REPLACE(column_name, query#1, query#2)

Is this possible?

I already have located the exact part of the content that i want to replace

SELECT `post_title`, SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) AS amazonlinks FROM wp_posts_duplicatedfortestonly WHERE `post_content` LIKE '%[flv:%'

and have the new string ready in another table column: Png_Link

Finally i'm trying to merge all this using the REPLACE function but it's giving me a lot of errors:

UPDATE wp_posts_duplicatedfortestonly 
SET 
    post_content = REPLACE(post_content,
                           (SELECT SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) FROM (SELECT post_content FROM wp_posts_duplicatedfortestonly) AS something WHERE `post_content` LIKE '%[flv:%'),
                           (SELECT `Png_Link`  FROM `VideoPlayerUdpateLinksReady` WHERE `Mp4_Link` != ''));

Something like this (only difference is i'm using 2 tables): https://www.db-fiddle.com/f/91EW2kJDc8aWJYYvw86vG9/0

Also tried with variables without any luck:

SET @tblname := "wp_posts_duplicatedfortestonly";
SET @colname := "post_content";
SELECT @lookfor := SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) FROM @tblname WHERE `post_content` LIKE '%[flv:%';
SELECT @replacewith := `Png_Link` FROM `VideoPlayerUdpateLinksReady` WHERE `Mp4_Link` != '';

SET @qry = CONCAT('UPDATE ',@tblname, ' SET ', @colname, ' = REPLACE(' ,@colname, ',', @lookfor, ',',@replacewith,')');
PREPARE QUERY FROM @qry;
EXECUTE QUERY;

What am i'm doing wrong? Is it possible to use SELECT expressions as REPLACE function parameters or what other workaround do i have?

  • Please define the task, not the way you decide to solve it... – Akina Jul 30 '19 at 19:48
  • What version of MySQL? – Rick James Jul 31 '19 at 23:43
  • Server version: 5.6.44 Thanks in advance – gallo2000sv Aug 1 '19 at 11:20
0

Managed to solve it on my own rookie way:

1- Since i couldn't make the REPLACE function to accept a query as parameter i just inserted the query result in a new table column:

INSERT INTO VideoPlayerUdpate_FLV_links (`ID`,`PostTitle`,`AmazonLinks`) SELECT `ID`, `post_title`, SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) AS amazonlinks FROM wp_posts WHERE `post_content` LIKE '%[flv:%'

Remember I had another table VideoPlayerUdpateLinksReady with all my new string that i wanted to replace. I had to manually construct and validate all errors in this column VideoPlayerUdpateLinksReady.Png_Link so it would be ready to replace.

2- So final Step was only to use these 2 tables as parameters in the REPLACE function:

UPDATE `wp_posts_duplicatedfortestonly`  JOIN `VideoPlayerUdpate_FLV_links` ON wp_posts_duplicatedfortestonly.ID = VideoPlayerUdpate_FLV_links.ID  JOIN `VideoPlayerUdpateLinksReady` ON wp_posts_duplicatedfortestonly.ID = VideoPlayerUdpateLinksReady.ID SET `post_content` = REPLACE(`post_content`,VideoPlayerUdpate_FLV_links.AmazonLinks, VideoPlayerUdpateLinksReady.Png_Link)

And i got all my content replaced without any further issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.