CODEWITHSUNDEEP
mysql
Shah Rushabh
Shah Rushabh

Reputation: 31

Mysql Know which column is matching

Table:

ID  English_name        Gujarati_name    English_short_name  Gujarati_short_name
1   2048 TWINKLE POUCH  ૨૦૪૮ ટ્વિન્કલ પાઉચ  2048 TWINKLE POUCH  ૨૦૪૮ ટ્વિન્કલ પાઉચ
2   2051 TWINKLE POUCH  ૨૦૫૧ ટ્વિન્કલ પાઉચ  2051 TWINKLE POUCH  ૨૦૫૧ ટ્વિન્કલ પાઉચ
3   1626 POUCH          ૧૬૨૬ પાઉચ        1626 POUCH          ૧૬૨૬ પાઉચ
4   1618 POUCH          ૧૬૧૮ પાઉચ        1618 POUCH          ૧૬૧૮ પાઉચ

Query:

SELECT `item`.`id`, 
       `item`.`english_name`, 
       `item`.`gujarati_name`, 
       `item`.`english_short_name`, 
       `item`.`gujarati_short_name` 
FROM   `item` 
WHERE  `english_name` LIKE '%twink%' escape '!' 
        OR `gujarati_name` LIKE '%twink%' escape '!' 
        OR `english_short_name` LIKE '%twink%' escape '!' 
        OR `gujarati_short_name` LIKE '%twink%' escape '!'

Expected Result:

ID  English_name        Gujarati_name    English_short_name  Gujarati_short_name  Matching
1   2048 TWINKLE POUCH  ૨૦૪૮ ટ્વિન્કલ પાઉચ  2048 TWINKLE POUCH  ૨૦૪૮ ટ્વિન્કલ પાઉચ  English_name, English_short_name 
2   2051 TWINKLE POUCH  ૨૦૫૧ ટ્વિન્કલ પાઉચ  2051 TWINKLE POUCH  ૨૦૫૧ ટ્વિન્કલ પાઉચ  English_name, English_short_name 
3   1626 POUCH          ૧૬૨૬ પાઉચ        1626 POUCH          ૧૬૨૬ પાઉચ
4   1618 POUCH          ૧૬૧૮ પાઉચ        1618 POUCH          ૧૬૧૮ પાઉચ

Anyone can please tell me How can I know which column is matching so I can print data only from that column, not from any other column. Please help me to edit this question so it may help others. and sorry if I made any grammatical mistake.

Upvotes: 1

Views: 32

Answers (2)

user4936563
user4936563

Reputation: 81

-- prototype     
SELECT IF('some phrase contains an item' like '%contain%', 'print-me', null) `my_column` FROM dual;
-- example 
SELECT 
    `item`.`id`, 
    IF (`english_name` LIKE '%twink%' escape '!', english_name, NULL) 
    english_name, 
    IF (`gujarati_name` LIKE '%twink%' escape '!', gujarati_name, NULL) 
gujarati_name /* more columns like that */
FROM   `item` 
WHERE  `english_name` LIKE '%twink%' escape '!' 
    OR `gujarati_name` LIKE '%twink%' escape '!' 
    OR `english_short_name` LIKE '%twink%' escape '!' 
    OR `gujarati_short_name` LIKE '%twink%' escape '!' ;

Thus, you can print only non-null fields.

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

It might look ugly to look for a column name which matches you criteria 

SELECT `item`.`id`, 
       `item`.`english_name`, 
       `item`.`gujarati_name`, 
       `item`.`english_short_name`, 
       `item`.`gujarati_short_name` ,
CONCAT_WS(
',',
CASE WHEN `english_name` LIKE '%twink%' ESCAPE '!'  THEN 'english_name' ELSE NULL END,
CASE WHEN `gujarati_name` LIKE '%twink%' ESCAPE '!'  THEN 'gujarati_name' ELSE NULL END,
CASE WHEN `english_short_name` LIKE '%twink%' ESCAPE '!'  THEN 'english_short_name' ELSE NULL END,
CASE WHEN `gujarati_short_name` LIKE '%twink%' ESCAPE '!'  THEN 'gujarati_short_name' ELSE NULL END
)  matching
FROM   `item` 
WHERE  `english_name` LIKE '%twink%' ESCAPE '!' 
        OR `gujarati_name` LIKE '%twink%' ESCAPE '!' 
        OR `english_short_name` LIKE '%twink%' ESCAPE '!' 
        OR `gujarati_short_name` LIKE '%twink%' ESCAPE '!'

Demo

Upvotes: 1

Related Questions