If I match a text string against multiple columns, is there an easy way to know which one(s) matched?

Question

I'm writing an advanced search functionality that returns records where a string matches one or more out of many text columns in this table.

Something like:

select * from some_table where (txt_1 like '%foo%') or (txt_2 like '%foo%') or...

Or:

select * from some_table where match (txt_1, txt_2, ...) against ('foo')

if I can do full-text search (not sure yet).

My issue is, how can I know which columns actually matched 'foo'?

For instance, if I have these records:

id  txt_1  txt_2  txt_3  txt_4
1   'foo'  'bar'  'bar'  'foo'
2   'bar'  'bar'  'bar'  'bar'
3   'bar'  'foo'  'bar'  'bar'
4   'bar'  'bar'  'bar'  'bar'

my query should return something like:

id  txt_1_matches  txt_2_matches  txt_3_matches  txt_4_matches
1   1              0              0              1
3   0              1              0              0

I can do this with post-processing on the simple query results, but I'd like to avoid it. Is there a clean, easy way to do this?

Cheers

Answer 1

You could do the search efficiently with a fulltext index, and then apply your first solution in the select-list (which runs only against rows that pass the filter in the WHERE clause):

select (txt_1 like '%foo%') as `txt1_matches`, 
       (txt_2 like '%foo%') as `txt2_matches`, ...
from some_table where match (txt_1, txt_2, ...) against ('foo')

You may also want to look into a more fully-featured full text search technology, like Sphinx Search or Apache Solr.

See for example the answer to How to return column that matched the query in Solr..?