Matching substrings from a delimited value in one table to the records in a second table, and building a join

Question

I recently dumped the records from an old CRM into a Navicat (MySQL) database as two tables.

__________________
| table_1:       |
|----------------|
| id             |
| delimited_col  |
------------------

__________________
| table_2:       |
|----------------|
| id             |
| name           |
------------------

Info:

• Table 1:
  • table_1.delimited_col consists of concatenated names delimited by ";"

    ex of a record: "kevin;nancy;james;bart;delaney"

• Table 2:
  • table_2.name consists of single names

    ex of a record: "kevin"

My Goal:

• Create a join table (table_3) which checks each record in table_1.delimited_col to see if they contain a value from table_2.name.
  • If a record in table_1.delimited contains a substring found in table_2.name, add that record from table_1's id and the matching record from table_2's id to the join table_3.

__________________
| join_table:    |
|----------------|
| id             |
| table_1_id     |
| table_2_id     |
------------------

I've been bashing my head against the wall trying to figure this out and would appreciate any help or advice!

Answer 1

You can use string method find_in_set():

select t1.id table_1_id, t2.id table_2_id
from table_1 t1
inner join table_2 t2 on find_in_set(t2.name, replace(t1.delimited_col, ';', ','))

But better yet, you should really fix your data model and store each value in the delimited list in a separate table row. Storing csv data in relational databases is not a good practice, and might hurt you in many ways. Here is a famous SO post that goes into details on this topic - actually, lots of questions related to delimited lists get closed as a duplicate to this post here on SO.

If you want to create a new table with the results of the query, then:

create table table_3(id int auto_increment, table_1_id, table_2_id);

insert into table_3 (table_1_id, table_2_id)
select t1.id, t2.id
from table_1 t1
inner join table_2 t2 on find_in_set(t2.name, replace(t1.delimited_col, ';', ','));