Recursive function improperly breaking up badly maintained CSV string field

Question

Here is a setup to demonstrate the problem:

create table foo (
  [goat] [varchar](20) NOT NULL,
  [email] [varchar](max) NULL,
  [id] [int] NOT NULL
)
GO

INSERT INTO foo (
  [goat],
  [email],
  [id]
)
values
  ('bluegoat', 'hello@goat.com', 1),
  ('bluegoat_addl_emails', 'goats@goat.com,cow@goat.com,wolf@goat.com,dog@goat.com,kitten4235@goat.com',1)
GO

select * from foo
GO --to test the setup, it should look like this:

goat                 email                                                                       id
bluegoat             hello@goat.com                                                               1
bluegoat_addl_emails goats@goat.com,cow@goat.com,wolf@goat.com,dog@goat.com,kitten4235@goat.com   1

/*here comes the fun part*/

with independent(goat, email, emailITEM, id) as
(
    SELECT [goat]
    ,left(email, CHARINDEX(',',email+',')-1)
    ,STUFF(email, 1, CHARINDEX(',',email+','), '')
    ,[id]
    FROM foo             
union all
    SELECT [goat]
    ,left(emailITEM, CHARINDEX(',',email+',')-1)
    ,STUFF(emailITEM, 1, CHARINDEX(',',email+','), '')
    ,[id]
    FROM independent
    where 
    emailITEM > ''
)

select * from independent
order by id

Using a modified version of this: Turning a Comma Separated string into individual rows I've attempted to break up the comma separated field. The original solution actually doesn't work with more than two CSV's, so I had to modify it to go through the field as many times as necessary.

goat                    email           emailITEM                                                       id
bluegoat                hello@goat.com                                                                  1
bluegoat_addl_emails    goats@goat.com   cow@goat.com,wolf@goat.com,dog@goat.com,kitten4235@goat.com    1
bluegoat_addl_emails     cow@goat.com,  olf@goat.com,dog@goat.com,kitten4235@goat.com                   1
bluegoat_addl_emails    olf@goat.com,   og@goat.com,kitten4235@goat.com                                 1
bluegoat_addl_emails    og@goat.com,    itten4235@goat.com                                              1
bluegoat_addl_emails    itten4235@g     at.com                                                          1
bluegoat_addl_emails    at.com                                                                          1

As you can see, it's supposed to start a new result at each comma, but the cursor keeps moving to the right, chopping off one additional character at the end of each line.

I know I'm close to the right solution and I can tell it's something with the numbers, but I can't seem to adjust the steps so that it stops breaking up the text at random points of the string.

Chances are, someone will look at this and immediately see the solution contrary to my exhausted brain, so I thought I'd give it a try here. Thank you so much to/if someone can give a hint.

Update: to adhere to the question format of S/o: How can I modify this function to make sure the string is chopped up right at each comma, discarding the commas and keeping the email addresses?

Jerrad · Accepted Answer · 2014-05-27T17:38:05.813

0

The second half of the union should look like this:

SELECT [goat]
,left(emailITEM, CHARINDEX(',',emailItem+',')-1)
,STUFF(emailITEM, 1, CHARINDEX(',',emailItem+','), '')
,[id]
FROM independent
where 
emailITEM > ''

Note emailItem in the CHARINDEX instead of email.

Comparing your code to the code in the post you referenced, you're swapping email and emailITEM around in a few places.

edited May 27 '14 at 17:38

answered May 27 '14 at 17:31

Jerrad

5,240
1
18
23

score 0 · Answer 2 · answered May 27 '14 at 17:57

Basically, clean up the list (replace spaces), and use charindex, and len to get the results you're looking for:

;with independent(goat, email, emailITEM, id) as
(
select
    goat
    ,left(email,CHARINDEX('|',email)-1) AS email
    ,right(email,len(email) - CHARINDEX('|',email)) AS EmailITEM
    , Id
from (
        select id, goat, replace(replace(email,',','|'),' ','') + '|' as email
        from foo             
        ) z
union all
    SELECT
        [goat]
        ,left(emailITEM,CHARINDEX('|',emailITEM)-1) AS email
        ,right(emailITEM,len(emailITEM) - CHARINDEX('|',emailITEM)) AS EmailITEM
        ,[id]
    FROM independent
    where 
    emailITEM > ''
)

Output:

goat                    email               emailITEM                                                       id
bluegoat                hello@goat.com                                                                      1
bluegoat_addl_emails    goats@goat.com      cow@goat.com|wolf@goat.com|dog@goat.com|kitten4235@goat.com|    1
bluegoat_addl_emails    cow@goat.com        wolf@goat.com|dog@goat.com|kitten4235@goat.com|                 1
bluegoat_addl_emails    wolf@goat.com       dog@goat.com|kitten4235@goat.com|                               1
bluegoat_addl_emails    dog@goat.com        kitten4235@goat.com|                                            1
bluegoat_addl_emails    kitten4235@goat.com                                                                 1

Recursive function improperly breaking up badly maintained CSV string field

2 Answers2