SQL Case statement Syntax

Question

I am trying to write a select statement that will select a number of fields including an email field. There are 3 email fields available in this table which can sometimes contains null values. I wan to look at 3 fields; [email address1], [email address2], [email address3] and basically what I want to do is if [email address3] is null then I want the value in [email address2], If email address2 is null than I want the value in [email address1]

I can't seem to get the Syntax right and am not too sure what I'm doing wrong.

Answer 1

What you need is COALESCE(...) function:

SELECT COALESCE(t.Email3, t.Email2, t.Email1) FROM MyTable t

Answer 2

No need for CASE, you can also use COALESCE ("Returns the first nonnull expression among its arguments"):

SELECT COALESCE(Email3, Email2, Email1) ...

Answer 3

See other answers about COALESCE, but if you really want a CASE structure, this should do it:

CASE
  WHEN email3 IS NOT NULL THEN email3
  WHEN email2 IS NOT NULL THEN email2
  WHEN email1 IS NOT NULL THEN email1
  ELSE ''
END

I added the else clause to prevent returning a Null from the whole thing, but if that's what you want to happen when all three are Null you can use:

CASE
  WHEN email3 IS NOT NULL THEN email3
  WHEN email2 IS NOT NULL THEN email2
  ELSE email1
END

Note that the SQL Server CASE function does not drop through cases--once it matches one, that's it. No need for a "break".