Mysql – How to use query result as REPLACE parameters

concatMySQLstring manipulationsubquerytable variable

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?

Best Answer

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.

Related Solutions

Mysql – How to use MySQL variables in subqueries

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows.

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

is translated to

SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\'');

MySQL variable cannot hold table resultset so this is not directly possible. You would have to create a subquery to extract separate values to rows by joining some counter table and parsing the string. Or you may use dynamic sql which allows to take a string and parse it - that way you would define new variable containing entire select with the IN list filled by your current variable, and then prepare a statement from that string representation (sort of SQL eval). But probably best would be to just use the query direcly as a subquery instead of filling the variable. Newer version should materialize such independent subquery automatically. For good performance on older versions, you might want to create a temporary table instead and use JOIN and not IN.

Just a note - GROUP_CONCAT() is not very safe as there is a limit (variable) to how long string it can return and some results may be lost (with a warning) when the limit is exceeded.

Sql-server – Use STUFF function to insert at multiple points in a string

You need to get it as rows before you can use STUFF.

DECLARE @rows nvarchar(max) = '[A],[B],[C]';
DECLARE @cols nvarchar(max);

SELECT LTRIM(RTRIM(j.value)) FROM STRING_SPLIT(@rows, ',') j

SET @cols = STUFF((SELECT ', ' + (col) 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ')') 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ') ' + col) 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;
GO

| (No column name) |
| :--------------- |
| [A]              |
| [B]              |
| [C]              |

| (No column name) |
| :--------------- |
| [A], [B], [C]    |

| (No column name)             |
| :--------------------------- |
| SUM([A]), SUM([B]), SUM([C]) |

| (No column name)                         |
| :--------------------------------------- |
| SUM([A]) [A], SUM([B]) [B], SUM([C]) [C] |

dbfiddle here

Best Answer

Related Solutions

Mysql – How to use MySQL variables in subqueries

Sql-server – Use STUFF function to insert at multiple points in a string

Related Question