拆分数据库行中的字符串并将结果复制到不同的行 - SQL Server [英] Split string in database row and copy results to different rows - SQL Server

问题描述

我创建了一个应用程序来向用户发送邮件.我正在使用一个数据库，其中一些用户分配了多个电子邮件地址.

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?

谢谢

推荐答案

这是一个使用 SQL Server 2005 及更高版本中的 XML 功能的简单示例.我已经从这里逐字逐句地摘录了它，但是如果您有很多例子谷歌拆分字符串sql server xml"

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.

这篇关于拆分数据库行中的字符串并将结果复制到不同的行 - SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！