CODEWITHSUNDEEP
mysqlsql-likematching
leepfrog
leepfrog

Reputation: 381

MySQL - Match reference table against concatenated string

I have problems achieving the following:

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.

Upvotes: 1

Views: 231

Answers (1)

valex
valex

Reputation: 24144

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

Upvotes: 2

Related Questions