Post reply

0 to many emails, transform into ID

  • September 9, 2015 at 11:53 am

    #307598

    Here's the scenario. I've got a column with an 0 to many emails.

    I want to lookup the email and replace it with an internal ID from a master table of emails

    For example, data could be

    column A

    john.doe@mail.com;jane.smith@mail.com;jane.doe@mail.com

    null

    steve.smith@mail.com

    john.doe@mail.com

    I want to lookup this email in the master email table and replace with the internal id's

    column A

    175;233;74

    null

    113

    175

  • September 9, 2015 at 12:02 pm

    #1825501

    This is a violation to the first normal form. Avoid storing several concatenated values and use an appropriate design for this.

    In order to be able to do what you want, you need to normalize the data. You could use a query with the DelimitedSplit8k which can be found in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply