a table of occurances of pattern in an SQL table field

Question

I have this table

text

| txt_id | txt_content                                  |
|--------+----------------------------------------------|
|      1 | A ton of text and <<this>>                   |
|      2 | More text <<and>> that                       |
|      3 | <<Very>> much <<text>> enough for<everyone>> |

And this table

tags

| tag_id | tag_name |
|--------+----------|
|      1 | THIS     |
|      2 | AND      |
|      3 | VERY     |
|      4 | TEXT     |
|      5 | EVERYONE |

And I need a query to yield this table.

| txt_id | tag_id |
|--------+--------|
|      1 |      1 |
|      2 |      2 |
|      3 |      3 |
|      3 |      4 |
|      3 |      5 |

It would be tivial to do with python code by getting each piece of text inidividually but the text table has many rows (>30M) and I think it would bee to much time on the database-backend communication. Is there a way to do this kind of thing with MySQL? I would even be content with

| txt_id | tag_id   |
|--------+----------|
|      1 | this     |
|      2 | and      |
|      3 | Very     |
|      3 | text     |
|      3 | everyone |

But that I expect the last part to be easy to do within MySQL.

Answer 1

This won't necessary be particularly fast, but it will do what you want:

select t.txt_id, ta.tag_id
from text t join
     tags ta
     on t.txt_content like concat('%<', ta.tag_name, '>%');