Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Question Unanswered: SSIS issue: How to handle comma delimited columns

    Hello

    I have this newbie question regarding SSIS:

    I have a table named People(#Code, Name, eMailList), where the column eMailList has a list of email addresses separated by commas, like "someone@hotmail.com,someone@gmail.com,someone@yah oo.com".

    I would like that for each Person, extract the email address info in such a way that I can insert into the following table: EMail(#Code, #ID, Address), where Address has a single email address.

    How can I accomplish that?

    Thanks a lot.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    I don't understand what this has to do with SSIS.

    There's no simple query that will split an item up, unfortunately, because you're not supposed to store structure that way. The right way of doing it is to split the email addresses into a separate table.

    To get it into a separate table, you've got to loop through each row and run an insert statement for each item in the list of email addresses. I'm not sure where you plan on getting that ID field from, unless it's a surrogate key. You can code the loop in T-SQL or in another language like VB if you're more comfortable.

    The advantage of putting them in a separate table is that you can implement integrity constraints. So if two people can't have the same email, you can enforce that with a simple candidate key constraint, a UNIQUE constraint in SQL.

  3. #3
    Join Date
    Mar 2003
    Posts
    144

    Arrow

    Perhaps I should have explained that the People table is populated from a Delimited Text file, and after loading the table by using SSIS, I would like to extract the individual email addresses as described.

  4. #4
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    Is there going to be a relationship between the People table and the EMail table? I notice both tables have a field named #Code. In other words, will all the email addresses for one person have the same #Code value, and will that value equal the #Code value for that person in the People table?

    sco08y is right. No really simple way to do it in SS. VB has a handy Split function that lets you choose the delimiter (a comma in your case) and it returns a string array.
    Don't Bogart That Squishee!

  5. #5
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    This forum can be pretty adamant about avoiding cursors, so here's a query to get all the individual email addresses:

    SELECT CASE WHEN CHARINDEX(',', emaillist, expr1) > 0 THEN substring(emaillist, expr1, CHARINDEX(',', emaillist, expr1) - expr1)
    ELSE substring(emaillist, expr1, len(emaillist) - (expr1 - 1)) END AS expr2
    FROM (SELECT p.emaillist, CHARINDEX(',', p.emaillist, i.i) + 1 AS Expr1
    FROM People p INNER JOIN
    ints i ON i.i BETWEEN 1 AND LEN(p.emaillist)
    GROUP BY p.emaillist, CHARINDEX(',', p.emaillist, i.i) + 1) t


    You'll need an integers table (named "ints" with one column named "i") containing consecutive integers from 0 to the length of the longest emaillist. So if the longest emaillist has 32 characters, the ints table's highest value must be at least 32.
    Don't Bogart That Squishee!

  6. #6
    Join Date
    Mar 2003
    Posts
    144

    Talking

    Thanks a lot!

  7. #7
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56

    Less mess

    That last query I posted looks like a big messql.
    Finding the comma positions was easy enough, but there's no comma after the last item or if there is only one item in the emaillist. So I was testing like this:

    If it's the last item... do this
    If it's the only item... do that
    Else... do the other

    But by adding a comma to the end of every emaillist, one expression will derive the items length for the Substring function without any conditional checking at all. Duh.
    When a square peg doesn't fit in a round whole, sometimes it's easiest just to round off the peg.

    SELECT SUBSTRING(xcomma, startpos, CHARINDEX(',', xcomma, startpos) - startpos) AS address
    FROM (SELECT p.emaillist + ',' AS xcomma, CHARINDEX(',', p.emaillist, i.i) + 1 AS startpos
    FROM People p INNER JOIN
    ints i ON i.i BETWEEN 1 AND LEN(p.emaillist)
    GROUP BY p.emaillist, CHARINDEX(',', p.emaillist, i.i) + 1) t
    Don't Bogart That Squishee!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •