CHOOSE DIFFERENTLY BASED ON WHERE

Question

I need to select a different value from the query depending on whether a certain part of the WHERE clause matches the result or not. While I have the logic of what needs to be achieved, I have absolutely no idea how to label this in a MySQL-friendly way!

Consider the following pseudo code:

SELECT table_name.*, `match_type` FROM table_name WHERE ( IF THIS MATCHES: 'input' LIKE CONCAT('%', `value`, '%'); THEN `match_type` equals 1 ELSEIF THIS MATCHES: `value` LIKE CONCAT('%', 'input', '%') `match_type` equals 0 ) 

If this first match finds any results, then I need to return a match_type column with a value of 1.

Otherwise, go to the second test. If this second match returns any results, then I need a match_type column so that it has a value of 0 instead.

If there are no matches, nothing needs to be returned. Thus, the only possible parameters for match_type should be 1 or 0.

---

How can i do this?

Thanks for your help!

Answer 1

MySql evaluates to TRUE as 1 , so you can only do this.

 SELECT table_name.*, ('input' LIKE CONCAT('%', `value`, '%')) match_type FROM table_name WHERE 'input' LIKE CONCAT('%', `value`, '%') OR `value` LIKE CONCAT('%', 'input', '%') 

Here is the SQLFiddle demo

Answer 2

Use CASE :

 SELECT table_name.*, CASE WHEN 'input' LIKE CONCAT('%', `value`, '%') THEN 1 ELSE 0 END AS match_type FROM table_name WHERE 'input' LIKE CONCAT('%', `value`, '%') OR `value` LIKE CONCAT('%', 'input', '%') 

Note. I used WHERE to limit the results to strings where one of the conditions is true, so I do not need to specify the second condition in CASE again.