Is there a way to fetch value of the column that was matched in where?-CodePudding

I am trying to develop a search algorithm. This is a problem I am facing right now.

name	Location
First	location 1
Second	location 2

Sql = select id, [matched_column_value] FROM table WHERE name = "%fir%" OR Location = "%fir%";

I want the value "First" in the matched_column_value.

If location column is matched the value should be of Location.

if anyone knows a function or method to solve this please let me know.

CodePudding user response：

Is not exist a simple way. The ugly way could be something like this

SELECT id,
    CASE
        WHEN name LIKE "%fir%" and location LIKE "%fir%" THEN "???"
        WHEN name LIKE "%fir%" THEN name
        WHEN location LIKE "%fir%" THEN location
        ELSE null
    END as match
FROM 
    table
WHERE 
    name LIKE "%fir%" OR Location LIKE "%fir%";