MySQL: Replace string in mediumtext

Question

I have a table with an entry of type mediumtext. This entry contains encoded website content with 40k+ chars like this: <!-- {"type":"layout","children":[{"type":"section","props":{"style":"default","width":"default","vertical_align":"middle","title_position":"top-left","title_rotation":"left","title_breakpoint":"xl"...

In the text there is the following span: ...<span id=\""myDate\"">26.05.2020<\/span>...

I would like to replace the date inside the span with my REGEXP_REPLACE-function which works when I use text:

UPDATE `jl72a_content` SET `fulltext` = REGEXP_REPLACE( 
    '...<span id=\""myDate\"">26.05.2020<\/span>...',
    '<span id=\"myDate\">(.*?)\\\/span>',
    '<span id=\"myDate\">my new value<\/span>')
WHERE id = 2;

But I have the problem that I can't use the `fulltext` as the string:

CREATE TEMPORARY TABLE temp SELECT `fulltext` FROM `jl72a_content` WHERE id = 2; 
UPDATE `jl72a_content` SET `fulltext` = REGEXP_REPLACE( 
    (SELECT `fulltext` FROM temp WHERE 1),
    '<span id=\"myDate\">(.*?)\\\/span>', 
    '<span id=\"myDate\">my new value<\/span>?')
WHERE id = 2;

I can't cast the result of the temporary table as a `mediumtext` and `varchar` is limited to 65,535.

How can I replace the span in this entry?

Does this answer your question? [RegEx match open tags except XHTML self-contained tags](https://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags) — Chase, Jun 16 '20 at 09:33
I can't imagine an other option to edit a value from that template framework without using the component. I have a batch script which uploads key figures files to a dashboard. The corresponding date should be updated automatically. Otherwise I have to write a content plugin which loads the date from an separate (new) data table. — quertx, Jun 16 '20 at 10:08

MySQL: Replace string in mediumtext

0 Answers0