bhishan bhishan - 6 months ago 14
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 1@yah.com linJ@gmail.com ty@mail.co
2 Sarah Woods ii@smiles.jp ube@mail.com SarahW@rou.com
3 Peter Wilkins wPeter@coors.au wsarah@yah.com null


Expected Result:

ContactId FirstName LastName Email
--------- --------- -------- ---------
1 Jeremy Lin linJ@gmail.com
2 Sarah Woods SarahW@rou.com
3 Peter Wilkins wPeter@coors.au

Answer

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

select 
    ContactId, FirstName, LastName,
    case 
        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.

Comments