bhishan bhishan - 4 months ago 6x
SQL Question

Select primary Email from multiple emails that match First or Last Name

I've got a really interesting requirement ahead of me. I thought about using cursors but I'm not sure if I can get what I want. I have a Contact table that may contain multiple emails per record like FirstName, LastName, Email1, Email2, Email3, Email4. So the requirement is to find email that contains either the first or last names and pick that email as their primary email. Is there any elegant way to write it in SQL?

ContactId FirstName LastName Email1 Email2 Email3
--------- --------- -------- --------- ------ ------
1 Jeremy Lin
2 Sarah Woods
3 Peter Wilkins null

Expected Result:

ContactId FirstName LastName Email
--------- --------- -------- ---------
1 Jeremy Lin
2 Sarah Woods
3 Peter Wilkins


I wouldn't call it elegant, but it's not complicated, and certainly doesn't require a cursor:

    ContactId, FirstName, LastName,
        when (Email1 like '%'+FirstName+'%' or Email1 like '%'+LastName+'%') then Email1
        when (Email2 like '%'+FirstName+'%' or Email2 like '%'+LastName+'%') then Email2
        when (Email3 like '%'+FirstName+'%' or Email3 like '%'+LastName+'%') then Email3
        else Email1 -- or whatever you want to use as the default
    end as Email
from Contacts

The above assumes you are using case-insensitive collation. If you're not, you'll need to add some upper() or lower() calls.