Split string in database row and copy results to d

问题:

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     [e-mail];[e-mail]

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         [e-mail]
BusinessXPTO         [e-mail]

What is the best solution?

Thanks

回答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.

回答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

回答3:

In Oracle

Here is a mock for provided example:

select 'BusinessXPTO' name, '[e-mail];[e-mail]' mail from dual;

Here is how to split the mail column into separate rows

select 'BusinessXPTO' name, regexp_substr('[e-mail];[e-mail]','[^;]+',1, rn) mail  
from dual 
cross join 
    (select rownum rn from 
        (select max(length(regexp_replace('[e-mail];[e-mail]', '[^;]+'))) + 1 mx from dual) 
    connect by level <= mx ) 
where regexp_substr ('[e-mail];[e-mail]', '[^;]+', 1, rn) is not null 
order by rn;

Here is the result

NAME            MAIL
BusinessXPTO    [e-mail]
BusinessXPTO    [e-mail]