SQL Server 2005: Select One Column If Another Column Is Null or Contains a Word

Question

I am trying to create a report using BIDS. I want one of the columns to provide an email address. I have two tables containing email addresses. One contains the original email address the customer provided when they started doing business with us. The other table contains a possibly updated (alternate) email address they submitted on the website.

Sometimes the original email address uses our company domain because my company use to create emails for clients that did not have an address.

I need to construct a query that will evaluate the original email address. It needs to do two things:

1. If the original email address is blank, it needs to include the alternate email address.
2. If the original email address contains our domain ([email protected]), it needs to include the alternate email address.
3. If the two items above are not the case, it needs to spit out the original email address.

The query will need to spit out this evaluation is a single column called Email.

Can this be done? Should I look towards BIDS instead? If so, what direction?

Thanks in advance for your help.

Solution 1

easy peasy using CASE. Something like:

SELECT whatever1, whatever2, CASE
WHEN originalemail IS NULL THEN alternateemail
WHEN originalemail like '%domainname%' THEN alternateemail
ELSE originalemail
END AS Email
FROM...

Solution 2

SELECT 

 CASE t1.orgEmail

   WHEN NULL THEN t2.altEmail

   WHEN LIKE '%domainname%' THEN t2.altEmail

   ELSE

      t1.orgEmail

  END AS email

FROM

table1 AS t1, Table2 as t2