Replace all occurrences of subquery in select result

Question

Working on an export from a Sparx EA database in MySQL.

The database contains objects that have notes

select o.Note from t_object o

The result could be

Note
Contains reference to term1 and term2
Another note that mentions term1 only
A note that doesn't mention any terms

There is also a glossary that I can query like this

select g.TERM 
  from t_glossary g
 union
select o.Name 
  from t_diagram d
  join t_diagramobjects dgo 
    on dgo.Diagram_ID = d.Diagram_ID
  join t_object o 
    on o.Object_ID = dgo.Object_ID
where 1=1
   and d.styleEx like '%MDGDgm=Glossary Item Lists::GlossaryItemList;%' 

The result of this query

TERM
term1
term2

The requirement is that I underline each word in the notes of the first query that is an exact match to one of the terms in the second query. Underlining can be done by enclosing the word in <u> </u> tags

So the final query result should be

Note
Contains reference to <u>term1</u> and <u>term2</u>
Another note that mentions <u>term1</u>only
A note that doesn't mention any terms

Is there any way to do this in a select query? (so without variables, temp tables, loops, and all that stuff)

Answer 1

I think regular expressions might be a better approach. For your example, you want:

select regexp_replace(note, '(term1|term2)', '<u>$1</u>')
from t_object;

You can easily construct this in MySQL as:

select regexp_replace(note, pattern, '<u>$1</u>')
from t_object cross join
     (select concat('(', group_concat(term separator '|'), ')') as pattern
      from t_glossary
     ) g;

Here is a db<>fiddle.

Regular expressions have a key advantage that they give you more flexibility on the word boundaries. The above replaces any occurrence of the terms, regardless of surrounding characters. But you can adjust that using the power of regular expressions.

I might also suggests that such replacement could be done -- using regular expressions -- at the application layer.