If else on WHERE clause

Question

I've this query:

SELECT  `id` ,  `naam`  FROM  `klanten`  WHERE ( `email` LIKE  '%@domain.nl%' OR  `email2` LIKE  '%@domain.nl%' ) 

But I want to do something like this:

SELECT  `id` ,  `naam`  FROM  `klanten`  WHERE IF(`email` > 0, `email` LIKE  '%@domain.nl%' ,  `email2` LIKE  '%@domain.nl%' ) 

How to check if email exist? I want to use email and if this field is empty I want to use email2. How do I accomplish this?

Answer 1

IF is used to select the field, then the LIKE clause is placed after it:

SELECT  `id` ,  `naam`  FROM  `klanten`  WHERE IF(`email` != '', `email`, `email2`) LIKE  '%@domain.nl%' 

Answer 2

You want to use coalesce():

where coalesce(email, email2) like '%[email protected]%' 

If you want to handle empty strings ('') versus NULL, a case works:

where (case when email is NULL or email = '' then email2 else email end) like '%[email protected]%' 

And, if you are worried about the string really being just spaces:

where (case when email is NULL or ltrim(email) = '' then email2 else email end) like '%[email protected]%' 

As an aside, the sample if statement is really saying "If email starts with a number larger than 0". This is because the comparison is to 0, a number. MySQL implicitly tries to convert the string to a number. So, '[email protected]' would fail, because the string would convert as 0. As would '[email protected]'. But, '[email protected]' and '[email protected]' would succeed.