I need something like a label, in which field the desired value was found, if in several - several labels respectively.

For example, there is a request

SELECT * FROM `table` WHERE (CONVERT(`a` USING utf8) LIKE '%$id%' OR CONVERT(`b` USING utf8) LIKE '%$id%' OR CONVERT(`c` USING utf8) LIKE '%$id%' )

And so that at the output I got not only the values ​​of the table, but also some labels, that the value was found in the fields a and c.

The answer is correct but slightly different.

 select *, concat(case when a LIKE '%$id%' then 'a' else '' end, case when b LIKE '%$id%' then 'b' else '' end, case when c LIKE '%$id%' then 'c' else '' end) fields FROM `table` WHERE (CONVERT(`a` USING utf8) LIKE '%$id%' OR CONVERT(`b` USING utf8) LIKE '%$id%' OR CONVERT(`c` USING utf8) LIKE '%$id%' )
  • something like select if(CONVERT(a USING utf8) LIKE '%$id%',1,0) as in_a, if(CONVERT(b USING utf8) LIKE '%$id%',1,0) as in_b,... fit? And if it's so creepy to have to look for some ID - then you probably have the wrong base structure - Mike
  • Well, I just inserted $ id =) And in fact there are words separated by commas. - Shevtsov Eugene
  • I 'll try your version - Shevtsov Eugene

1 answer 1

 select *, concat(case when a LIKE '%$id%' then 'a' else '' end, ' ', case when b LIKE '%$id%' then 'b' else '' end, ' ', case when c LIKE '%$id%' then 'c' else '' end) fields FROM `table` WHERE (CONVERT(`a` USING utf8) LIKE '%$id%' OR CONVERT(`b` USING utf8) LIKE '%$id%' OR CONVERT(`c` USING utf8) LIKE '%$id%' )
  • It seems to be what you need, but for some reason, the output fields are always null - Shevtsov Eugene
  • Replace concat with concat_ws - msi
  • I corrected the decision. Should work without concat_ws. - msi
  • Thank. I have already added a question, actually corrected your answer. - Shevtsov Eugene