Splitting an SQL string into multiple strings

Question

I am trying to split a single string containing multiple email address data into three variables. The strings mark the start/end of an email address with the ; character.

An example string would be:

'joebloggs@gmailcom;[email protected];[email protected]'

The code I currently have for this is as follows:

    DECLARE @Email VARCHAR(100),
        @Email2 VARCHAR(100),
        @Email3 VARCHAR(100)

SET @Email = 'joebloggs@gmailcom;[email protected];[email protected]'

SET @Email2 = SUBSTRING(@Email, CHARINDEX(';', @Email)+1, LEN(@Email))
SET @Email3 = SUBSTRING(@Email, CHARINDEX(';', @Email)+1, LEN(@Email))
SET @Email = SUBSTRING(@Email, 1, CHARINDEX(';', @Email)-1)

Unfortunately this doesn't seem to work. Could someone please point out where I am going wrong and what I should do to fix my problem?

Thanks in advance.

Answer 1

Assuming that there will always be 3 email addresses - the following seems to work;

DECLARE @Email VARCHAR(100),
        @Email2 VARCHAR(100),
        @Email3 VARCHAR(100)

SET @Email = 'joebloggs@gmailcom;[email protected];[email protected]'

SELECT   @Email     = LEFT(@Email, CHARINDEX(';', @Email) - 1)
        ,@Email2    = SUBSTRING (   
                                    @Email, 
                                    CHARINDEX(';', @Email) + 1, 
                                    CHARINDEX(';', @Email, CHARINDEX(';', @Email) + 1) - LEN(LEFT(@Email, CHARINDEX(';', @Email) )) - 1
                                )
        ,@Email3    = RIGHT(@Email, CHARINDEX(';', @Email)-1)

Answer 2

This solution:

create function dbo.SplitString 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS Item
from tokens
);

GO

Taken from How do I split a string so I can access item x