Agfdhyk

given a mysql query in the form like this:

SELECT *

FROM table

WHERE field1 LIKE '%foo%'

    OR field2 LIKE '%bar%'

    OR field3 LIKE '%foobar%'

Which is the best way to tag the "matching fields" ?

For example:

+----+--------+--------+----------+

| ID | field1 | field2 |  field3  |

+----+--------+--------+----------+

| 01 |   foo  |   xxx  |    xxx   |

+----+--------+--------+----------+

| 02 |   xxx  |12bar21 |xxfoobarxx|

+----+--------+--------+----------+

| 03 |   f2o  |   bar  |    yxz   |

+----+--------+--------+----------+

Here every record would be matched - now I want to know which field for each record matches. (So for ID 01 it is field1, for ID 02 it is field2, field3 and for ID 03 it is field2)

Thank you

You can add flags using conditionals to your select like:

SELECT *,

    IF(field1 LIKE '%foo%', 1, 0) AS field1_matches,

    IF(field2 LIKE '%bar%', 1, 0) AS field2_matches,

    IF(field3 LIKE '%foobar%', 1, 0) AS field3_matches

FROM TABLE

WHERE field1 LIKE '%foo%'

    OR field2 LIKE '%bar%'

    OR field3 LIKE '%foobar%'

If you need the results in a single field as a comma separated list you can concatenate the results using CONCAT_WS and return NULL inside your conditional when there's no match:

SELECT *,

    CONCAT_WS(',',

        IF(field1 LIKE '%foo%', 'field1', NULL),

        IF(field2 LIKE '%bar%', 'field2', NULL),

        IF(field3 LIKE '%foobar%', 'field3', NULL)

    ) AS matches

FROM TABLE

WHERE field1 LIKE '%foo%'

    OR field2 LIKE '%bar%'

    OR field3 LIKE '%foobar%'

How about a CASE expression:

SELECT

    CASE WHEN field1 REGEXP '[[:<:]]foo[[:>:]]|[[:<:]]bar[[:>:]]|[[:<:]]foobar[[:>:]]'

         THEN 'field1' ELSE '' END AS field1match,

    CASE WHEN field2 REGEXP '[[:<:]]foo[[:>:]]|[[:<:]]bar[[:>:]]|[[:<:]]foobar[[:>:]]'

         THEN 'field2' ELSE '' END AS field2match,

    CASE WHEN field3 REGEXP '[[:<:]]foo[[:>:]]|[[:<:]]bar[[:>:]]|[[:<:]]foobar[[:>:]]'

         THEN 'field3' ELSE '' END AS field3match

FROM yourTable

Demo

If you just want to match substrings, then use this:

SELECT

    CASE WHEN field1 REGEXP 'foo|bar|foobar'

         THEN 'field1' ELSE '' END AS field1match,

    CASE WHEN field2 REGEXP 'foo|bar|foobar'

         THEN 'field2' ELSE '' END AS field2match,

    CASE WHEN field3 REGEXP 'foo|bar|foobar'

         THEN 'field3' ELSE '' END AS field3match

FROM yourTable

Using CONCAT and CASE

You are exactly looking for the below result but I know you wish to separate fields with comma or any other sign, for this I can't access mysql now so I think you can manage that by yourself

SELECT *,

    CONCAT(CASE WHEN field1 LIKE '%foo%' THEN 'field1' ELSE NULL END,

            CASE WHEN field2 LIKE '%bar%' THEN 'field2' ELSE NULL END,

            CASE WHEN field3 LIKE '%foobar%' THEN 'field3' ELSE NULL END) AS macthed_column

FROM youtable

WHERE field1 LIKE '%foo%'

    OR field2 LIKE '%bar%'

    OR field3 LIKE '%foobar%'

OUTPUT:

ID  field1  field2  field3      macthed_column

1   foo     xxx     xxx         field1

2   xxx     12bar21 xxfoobarxx  field2field3

3   f2o     bar     yxz         field2