How to skip an empty LIKE operator in a multiple LIKE query?

Question

I notice my query doesn't behave correctly if one of the like variables is empty:

SELECT name 
FROM   employee 
WHERE  name LIKE '%a%' 
AND     color LIKE '%A%' 
AND     city LIKE '%b%' 
AND     country LIKE '%B%' 
AND     sport LIKE '%c%' 
AND     hobby LIKE '%C%' 

Now when a and A are not empty it works but when a, A and c are not empty the c part is not excuted so it seems?

How can I fix this?

Answer 1

I'm assuming that you want to do a filter where you only check the value if the filter value is non-null. In this case, check for nullity in the variable as an alternative condition.

select name
from employee
where (@a is null or name like '%' + @a + '%')
   and (@b is null or color like '%' + @b + '%')
   ...

Answer 2

Use the IF() function....

SELECT name 
  FROM employee 
 WHERE ( IF(LENGTH('%a') > 0, name LIKE '%a%' , 0)
    OR IF(LENGTH('%A') > 0, color LIKE '%A%', 0)
    OR IF(LENGTH('%b') > 0, city LIKE '%b%' , 0)
    OR IF(LENGTH('%B') > 0, country LIKE '%B%', 0) 
    OR IF(LENGTH('%c') > 0, sport LIKE '%c%' , 0)
    OR IF(LENGTH('%C') > 0, hobby LIKE '%C%' , 0) 
       )

The where clause of a sql query is looking for a 'true/false' result from the AND/OR logic. Thus you can return an expression to be TESTed, or the result of a test, or a raw hard true/false value from the IF() function.

-- J Jorgenson --