sql - Replacing IsNull and LEN with Case and IS NULL

Question

I am using always encrypted and couldn't use IsNull or LEN function. I want to use case and IS NULL or IS NOT NULL to achieve that.

Can someone let me know how to re-write the below logic using CASE and IS NULL/ IS NOT NULL?

Len(IsNull(c.email1, IsNull(e.email,ORG_Email))) <> 0

Update 2 :

case when email_indicator != 'N' and Len(IsNull(c.email1, IsNull(e.email,ORG_Email))) <> 0

Then 'E' else 'N' End

Answer 1

I would write this as:

len(coalesce(c.email1, e.email, org_email)) <> 0

but you probably have the same problem. So the answer to your question using case and is null is:

(case when c.email1 is null and e.email is null and org_email is null
      then 0   -- all are missing
      else 1
 end) = 1

I don't like case statements in the where clause, so the better answer is the simpler expression:

(c.email1 is not null or e.email is not null or org_email is not null)

This is really the right way to express the logic.