How to use multiple LIKE operators for a search filter query??

Hi,

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??
regards

 

You may need to use a mix of OR's and AND's. With all ANDs, every single column must match to return a result.

This would return a name if any matched.
SELECT name
FROM employee
WHERE name LIKE '%a%'
OR color LIKE '%A%'
OR city LIKE '%b%'
OR country LIKE '%B%'
OR sport LIKE '%c%'
OR hobby LIKE '%C%'

If there's a few columns that must match while others are optional you would mix it up like:
SELECT name
FROM employee
WHERE (name LIKE '%a%' AND city LIKE '%b%' AND country LIKE '%B%')
AND (sport LIKE '%c%' OR hobby LIKE '%C%' OR color LIKE '%A%')

Unless your database collation is using _cs at the end of the table/column definitions, you don't need to capitalize your searched for terms. The _ci means case_insensitive.

Also, are you sure it isn't inserting a space '% %' on an empty value? This would require a space be in the column for a positive match.

 

like commend syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern


example

SELECT name
FROM employee
WHERE name LIKE '%a%'