MySQL - Match reference table against concatenated string

Question

I have problems achieving the following:

• I have a "reference table" consisting only of strings
• I have a "data table" which does have a field containing a string which is concatenated from several strings which can occur in the reference table. The syntax which delimits the strings varies - so they cannot be splitted easily.

What I need is a query which runs through the reference table and searches if the data table has rows where this concatenated string contains values from the reference table.

Also it would be perfect if the query would return the PK of the data row as well as the string(s) which were found from the reference table.

Answer 1

Try this query. It can output dublicates from data_tables if it contains more than one string from reference_table:

select * from data_table
join reference_table 
on (data_table.concatenated_field 
               like CONCAT('%',reference_table.string_field,'%'));

Also you can group these dublicate rows from data_tables in one:

select data_table.id, max(data_table.concatenated_field),
GROUP_CONCAT(reference_table.string_field) 
from data_table
join reference_table 
on (data_table.concatenated_field 
               like CONCAT('%',reference_table.string_field,'%'))
group by data_table.id