MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(6) unsigned NOT NULL,
  `display_text` text NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `id_translations` (
  `old_id` int(6) unsigned NOT NULL,
  `new_id` int(6) unsigned NOT NULL,
  PRIMARY KEY (`old_id`, `new_id`)
);

INSERT INTO `comments` (`id`, `display_text`) VALUES
(1, 'Hey @{{"user":12}}@, look at this'),
(2, 'Thats pretty cool'),
(3, '@{{"user":41}}@ could you take a peek at this?'),
(4, 'This id a string @{{"user":12}}@ which contains 2 patterns @{{"user":41}}@ to be replaced.');

SELECT * FROM comments;

INSERT INTO `id_translations` (old_id, new_id) VALUES (12, 100), (41, 101);

SELECT * FROM id_translations;


id
display_text


1
Hey @{{"user":12}}@, look at this

2
Thats pretty cool

3
@{{"user":41}}@ could you take a peek at this?

4
This id a string @{{"user":12}}@ which contains 2 patterns @{{"user":41}}@ to be replaced.




old_id
new_id


12
100

41
101


CREATE PROCEDURE replacing ()
BEGIN
    REPEAT
        UPDATE comments
        SET display_text = CONCAT( LEFT(display_text, LOCATE('@{{"user":', display_text) - 1),
                                   CHAR(0),
                                   ( SELECT new_id 
                                     FROM id_translations 
                                     WHERE old_id = 0 + SUBSTRING(display_text FROM LOCATE('@{{"user":', display_text) + 10)),
                                     CHAR(1),
                                   SUBSTRING(display_text FROM LOCATE('}}@', display_text) + 3))
        
        WHERE LOCATE('@{{"user":', display_text);
    UNTIL !ROW_COUNT() END REPEAT;
    UPDATE comments
    SET display_text = REPLACE(REPLACE(display_text, CHAR(0), '@{{"user":'), CHAR(1), '}}@');
END
CALL replacing();
SELECT * FROM comments;


id
display_text


1
Hey @{{"user":100}}@, look at this

2
Thats pretty cool

3
@{{"user":101}}@ could you take a peek at this?

4
This id a string @{{"user":100}}@ which contains 2 patterns @{{"user":101}}@ to be replaced.

id	display_text
1	Hey @{{"user":12}}@, look at this
2	Thats pretty cool
3	@{{"user":41}}@ could you take a peek at this?
4	This id a string @{{"user":12}}@ which contains 2 patterns @{{"user":41}}@ to be replaced.

id	display_text
1	Hey @{{"user":100}}@, look at this
2	Thats pretty cool
3	@{{"user":101}}@ could you take a peek at this?
4	This id a string @{{"user":100}}@ which contains 2 patterns @{{"user":101}}@ to be replaced.