CODEWITHSUNDEEP
sqlsql-server-2012cursor
bhishan
bhishan

Reputation: 7

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     [email protected]         [email protected]     [email protected]
        2       Sarah        Woods     [email protected]      [email protected]       [email protected]
        3       Peter      Wilkins     [email protected]   [email protected]     null

Expected Result:

ContactId   FirstName     LastName     Email
---------   ---------     --------     ---------
        1      Jeremy          Lin     [email protected]
        2       Sarah        Woods     [email protected]       
        3       Peter      Wilkins     [email protected]

Upvotes: 0

Views: 635

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103585

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.

Upvotes: 3

Related Questions