** Split delimited field into multiple fields **

[jonnywah]

I have 2 fields email1 and email2 in a Email Table. These fields contain multiple email addresses separated by commas. There can be up to a 6 emails in the 2 fields.

For Example:
Email1 can be "joe@joe.com, joe2@joe.com" and Email2 can be "joe3@joe.com, joe5@abc.com"

Concataned value of Email1 and Email2 would be "joe@joe.com, joe2@joe.com, joe3@joe.com, joe5@abc.com"

I want to split the emails in email1 and email2 (sepaated by commas) into multiple fields emailM1, emailM2, emailM3, emailM4, emailM5, and emailM6. If the total email addresses is less than 6 emails (as in the above example), emailM5 and emailM6 would be set to "".

How can I use an UPDATE sql statement to do this? Please help. Thank you in advance.

 

[johnherman]

Pseudocode
1. Trim all trailing spaces from e-mail1
2. Concatenate e-mail1 and e-mail2 inserting a comma between them.
3. Count the commas in the new combined string and add 1. This is the number of e-mails you will be getting.
4. Locate the position of the first comma in the string.
5. Parse out e-mail1.
6. Repeat for the number of e-mails you determined above.
7. Set to null the remaining e-mails.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[lespaul]

you might be better off normalizing your data:


TblCustomer
customerID
customerName
etc...

tblCustomerEmail
CustomerID
EmailID
EmailAddress

with a composite Primary Key in tblCustomerEmail of CustomerID and EmailID

 

[SQLSister]

Agree with lespaul, this is a poor table design. The first rule is never store more than one piece of information in a field.

 

[jonnywah]

This is definitely poor table design, but I am not allowed to changed the current structure because it will break other developers' code.

johnherman, I don't know how to code step 3 to 5? I am fairly new to sql. Please advise. Thank you.

 

[TheDrider]

This is one way. I'm not sure if you're trying to update the entire table at once or just a specific record. The code below will give you start to update one record. It's a basic list-parsing routine. It concatenates the two email fields, then runs through list and updates the correct DB field. You'll have to tweak a little for your specific usage.


Declare @list varchar(3000)
Declare @nextPos int
Declare @element varchar(500)

Declare @nextEmail tinyint
SET @nextEmail = 1

SET @list = Email1 + ',' + Email2

WHILE Len(@list) > 0
BEGIN
SET @nextPos = CharIndex(',', @list)
IF @nextPos = 0 SET @nextPos = Len(@list) + 1

SET @element = SubString(@list, 1, @nextPos - 1)
SET @list = SubString(@list, @nextPos + 1, 3000)

-- We have an email address, send it to the database
IF @nextEmail = 1
BEGIN UPDATE your_table SET EMAILM1 = @element WHERE your_ID = some_ID END
ELSE IF @nextEmail = 2
BEGIN UPDATE your_table SET EMAILM2 = @element WHERE your_ID = some_ID END
ELSE IF @nextEmail = 3
BEGIN UPDATE your_table SET EMAILM3 = @element WHERE your_ID = some_ID END
ELSE IF @nextEmail = 4
BEGIN UPDATE your_table SET EMAILM4 = @element WHERE your_ID = some_ID END
ELSE IF @nextEmail = 5
BEGIN UPDATE your_table SET EMAILM5 = @element WHERE your_ID = some_ID END
ELSE IF @nextEmail = 6
BEGIN UPDATE your_table SET EMAILM6 = @element WHERE your_ID = some_ID END

-- increment the email counter
SET @nextEmail = @nextEmail + 1
END

 

[TJRTech]

I think the following stored procedure will work:

CREATE PROCEDURE [dbo].[SP_GET_EMAIL_ADDRESSES]
	@email1_col varchar(255),
 	@email2_col varchar(255),
 	@email1 varchar(255) OUTPUT,
	@email2 varchar(255) OUTPUT,
 	@email3 varchar(255) OUTPUT,
	@email4 varchar(255) OUTPUT,
	@email5 varchar(255) OUTPUT,
 	@email6 varchar(255) OUTPUT,
	@num_email int OUTPUT
AS

DECLARE @email_list varchar(511)  -- Full email list from both fields

DECLARE @i int  -- Loop counter
DECLARE @ndx int -- Index of , character

-- Remove whitespace from email fields
SET @email1_col = replace(@email1_col, ' ', '')
SET @email2_col = replace(@email2_col, ' ', '')

-- Build concatenated list from two fields
IF @email1_col<>'' and @email2_col<>''
  SET @email_list=@email1_col + ',' + @email2_col
ELSE
IF  @email1_col<>'' and @email2_col=''
  SET @email_list=@email1_col
ELSE
IF  @email1_col='' and @email2_col<>''
  SET @email_list=@email2_col
ELSE
  SET @email_list=''

-- Init variables
SET @num_email = 0
SET @email1 = ''
SET @email2 = ''
SET @email3 = ''
SET @email4 = ''
SET @email5 = ''
SET @email6 = ''

-- If @email_list is empty we are done
IF @email_list='' RETURN(0)

-- Get number of emails in list then loop through list
SET @num_email = LEN(@email_list) - LEN(replace(@email_list, ',', '') ) + 1
SET @i=1
SET @email_list = @email_list + ','  -- Last email will be followed by comma
WHILE @i <= @num_email
  BEGIN
	-- Find next comma
        SET @ndx = CHARINDEX(',', @email_list)  

        -- Set appropriate email output variable
        IF @i=1 
	    SET @email1 = LEFT(@email_list, @ndx-1)
        ELSE 
        IF @i=2 
	    SET @email2 = LEFT(@email_list, @ndx-1)
        ELSE 
        IF @i=3
	    SET @email3 = LEFT(@email_list, @ndx-1)
        ELSE 
        IF @i=4
	    SET @email4 = LEFT(@email_list, @ndx-1)
        ELSE 
        IF @i=5
	    SET @email5 = LEFT(@email_list, @ndx-1)
        ELSE 
        IF @i=6
	    SET @email6 = LEFT(@email_list, @ndx-1)

        -- Shorten list to account for email just processed
        SET @email_list = RIGHT(@email_list, LEN(@email_list)-@ndx)
       
        -- Increment and continue
       SET @i=@i+1
  END

RETURN (0)

Here is some SQL that tests the stored proc:

-- Declare variables
DECLARE @email1_col varchar(255)
DECLARE @email2_col varchar(255)
DECLARE @email1 varchar(255)
DECLARE @email2 varchar(255)
DECLARE @email3 varchar(255)
DECLARE @email4 varchar(255)
DECLARE @email5 varchar(255)
DECLARE @email6 varchar(255)
DECLARE @num_email int

-- Set input variables
SET @email1_col = 'joe@nspam.com,test1@yahoo.com'
SET @email2_col = 'jane@nspam.com,test2@yahoo.com'

-- Exec the stored proc
EXEC SP_GET_EMAIL_ADDRESSES
     @email1_col,
     @email2_col,
     @email1 OUTPUT,
     @email2 OUTPUT,
     @email3 OUTPUT,
     @email4 OUTPUT,
     @email5 OUTPUT,
     @email6 OUTPUT,
     @num_email OUTPUT

-- Select out the results
SELECT @num_email as NUM_EMAIL,
       @email1 as EMAIL_1,
       @email2 as EMAIL_2,
       @email3 as EMAIL_3,
       @email4 as EMAIL_4,
       @email5 as EMAIL_5,
       @email6 as EMAIL_6

 

[jonnywah]

Everyone especially thedrider and TJRTech,

I got everything working 100%. Thank you!!