How to replace strings in brackets with <span> tags in MySQL

I have thousands of string in a MySQL table in a column called query of type varchar(4096) which look always similar to the following one. The brackets are not always the same number! That means that in one query there could be 3 variables in brackets and in another on 7 variables in brackets.

Sample

“How can we compare the total rewards [team], taking into account that [industry] is better? Provide metrics for analysis, including [costs], and [best practices]. Additionally suggest any [potential areas for improvement] based on your analysis.”

What i want to do is to replace these brackets and put them in <span class="variable-1"> where 1 should be counting for the amount of existing brackets:

Expected result

“How can we compare the total rewards <span class="variable-1">[team]</span>, taking into account that <span class="variable-2">[industry]</span> is better? Provide metrics for analysis, including <span class="variable-3">[costs]</span>, and <span class="variable-4">[best practices]</span>. Additionally suggest any <span class="variable-5">[potential areas for improvement]</span> based on your analysis.”

How can i do this directly within MySQL?

A compromise between efficiency and effectiveness, is assigning the name "variable" to all your variables, and then gather all variables with tools belonging to higher than sql layers. Attempting to assign a different id to each variable could lead you to some kind of recursive query, which would reveal highly inefficient (especially if you're dealing with thousands of strings).

Assuming having the same variable name could work for you as well, you can try using REGEXP_REPLACE with backreference. In order to make sure the matched text does not include other closed parentheses, we can use the lazy operator ?.

SELECT REGEXP_REPLACE(txt, 
                      '\\[(.*?)\\]',
                      '<span class="variable">$1</span>') AS txt
FROM tab

If the pattern matching looks too relaxed due to '.*?', you can restrict your available values among brackets [A-Za-z ]+?:

SELECT REGEXP_REPLACE(txt, 
                      '\\[([A-Za-z ]+?)\\]', 
                      '<span class="variable">$1</span>') AS txt
FROM tab;

or match any character except closed bracket [^\\[]+:

SELECT REGEXP_REPLACE(txt, 
                      '\\[([^\\[]+)\\]', 
                      '<span class="variable">$1</span>') AS txt
FROM tab;

Output:

Check the demo here.

Assuming a table foo with a column bar to change and an id primary key:

update foo
join (
    select id, group_concat(case when i then concat('<span class="variable-',i,'">[',replace(substring_index(substring_index(bar,'[',i+1),'[',-1),']',']</span>')) else substring_index(bar,'[',1) end order by i separator '') newbar
    from foo
    join (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10) i
        on i <= char_length(bar)-char_length(replace(bar,'[',''))
    group by id
) newbar using (id)
set bar=newbar

fiddle

This splits up each string based on the number of left brackets and adds the spans to each part beginning with a bracket, then rejoins them together.

char_length...-char_length(replace... is how you count the number of substrings in a string.

substring_index(substring_index(...,delim,i+1),delim,-1) is how you extract the ith part of a string delimited by delim.

I have the following MySQL SQL googled:

SET @counter := 0;
UPDATE table1 SET prompt = REGEXP_REPLACE(columnA COLLATE utf8mb4_general_ci, '\\[([^\\]]*)\\]', CONCAT('<span class="prompt-variable-', @counter := @counter + 1, '">', '\</span>'));

Seems to be a very slim and simple solution. Replace table1 and columnA. It creates a variable named counter. With Regex it searched for brackets [] and add the html span around it.