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%";