Split string in database row and copy results to different rows - SQL Server

Question

Hello.

I created an application to send mail to users. I'm using a database where some users have more than one email address assigned.

Name                        Mail
-----------------------------------------
BusinessXPTO     [email protected];[email protected]

The email column can contain more than one email, separated by a semicolon. I want to split the string so I can put each email on a different line.

Name                        Mail
-----------------------------------------
BusinessXPTO         [email protected]
BusinessXPTO         [email protected]

What is the best solution?

Thanks

Answer 1

Here's a simple example using the XML features in SQL Server 2005 and above. I've taken it verbatim from here but there are lots of examples if you google "split string sql server xml"

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

There are other solutions with cursors but this approach as worked well for me.

Answer 2

The following is a UDF that I use to split values on an arbitrary delimiter. I've since modified my function so that it is an inline table function (instead of a multi-statement table function) and no longer relies on a Numbers table as it builds it on the fly.

CREATE FUNCTION dbo.udf_Split
(   
    @DelimitedList nvarchar(max)
    , @Delimiter nvarchar(2) = ','
)
RETURNS TABLE 
AS
RETURN 
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , Len(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.columns As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value < Len(CL.List)
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )

Here's an example call:

Select T.Name, Address.Value
From MyTable As T
    Cross Apply dbo.udf_Split( T.Mail, ';' ) As Addresses