在 SQL Server 中将字符串拆分为给定行分隔符和列分隔符的表

Split string into table given row delimiter and column delimiter in SQL server(在 SQL Server 中将字符串拆分为给定行分隔符和列分隔符的表)

问题描述

如何在 SQL Server 中将包含矩阵的字符串拆分为表?字符串具有列和行分隔符.

How to split string containing matrix into table in SQL Server? String has columns and row delimiters.

假设我有一个字符串:

declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';

预期结果(在三个单独的列中):

Expected results (in three separate columns):

+---+---+---+
| A | B | C |
+---+---+---+
| D | E | F |
+---+---+---+
| X | Y | Z |
+---+---+---+

我正在寻找未定义列数和行数的通用解决方案.所以字符串:

I am looking for general solution which has not defined number of columns and rows. So the string:

declare @str varchar(max)='A,B;D,E';

将被分成两列的表格:

+---+---+
| A | B |
+---+---+
| D | E |
+---+---+

我的努力.我的第一个想法是使用动态 SQL 将字符串转换为:insert into dbo.temp values (...) 这种方法虽然非常快，但有一个小缺点，因为它需要先创建一个具有正确列数的表.我在下面我自己的问题的答案中介绍了这种方法，只是为了使问题简短.

My efforts. My first idea was to use dynamic SQL which turns the string into: insert into dbo.temp values (...) This approach although very fast has a minor drawback because it requires creating a table with the right number of columns first. I have presented this method in the answer to my own question below just to keep the question short.

另一个想法是将字符串写入服务器上的 CSV 文件，然后从中批量插入.虽然我不知道该怎么做以及第一个和第二个想法的表现如何.

Another idea would be to write down the string to a CSV file on the server and then bulk insert from it. Though I do not know how to do it and what would be performance of first and second idea.

之所以问这个问题，是因为我想将数据从Excel导入SQL Server.由于我尝试过不同的 ADO 方法，这种发送矩阵字符串的方法是压倒性的胜利，尤其是当字符串的长度增加时.我在这里问了一个问题的孪生弟弟:将 Excel 范围转换为 VBA 字符串 在哪里可以找到有关如何从 Excel 范围准备此类字符串的建议.

The reason why I asked the question is because I want to import data from Excel to SQL Server. As I have experimented with different ADO approaches, this method of sending matrix-string is a landslide victory, especially when the length of the string increases. I asked a younger twin brother of the question here: Turn Excel range into VBA string where you will find suggestions how to prepare such a string from Excel range.

赏金 我决定奖励马特.我高度评价肖恩·兰格的回答.谢谢肖恩.我喜欢马特的答案，因为它简单而简短.除了马特和肖恩的不同方法可以并行使用，所以暂时我不接受任何答案(更新:最后，几个月后，我接受了马特的答案).我要感谢 Ahmed Saeed 对 VALUES 的想法，因为这是我开始的答案的一个很好的演变.当然，它不是马特或肖恩的对手.我赞成每一个答案.我将感谢您对使用这些方法的任何反馈.谢谢你的追求.

Bounty I decided to award Matt. I weighed highly Sean Lange's answer. Thank you Sean. I liked Matt's answer for its simplicity and shortness. Different approaches apart from Matt's and Sean's could be in parallel use so for the time being I am not accepting any answer (update: Finally, after a few months, I have accepted Matt's answer). I wish to thank Ahmed Saeed for his idea with VALUES, for it is a nice evolution of the answer I began with. Of course, it is no match for the Matt's or Sean's. I upvoted every answer. I will appreciate any feedback from you on using these methods. Thank you for the quest.

推荐答案

一种更简单的方法是基于替换分隔符将字符串转换为 XML.

One of the easier ways is to convert the string to XML based on replacing your delimiters.

declare @str varchar(max)='A,B,C;D,E,F;X,Y,Z';
DECLARE @xmlstr XML
SET @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)

SELECT
    t.n.value('col[1]','CHAR(1)') as Col1
    ,t.n.value('col[2]','CHAR(1)') as Col2
    ,t.n.value('col[3]','CHAR(1)') as Col3
FROM
    @xmlstr.nodes ('/rows/row') AS t(n)

• 将字符串格式化为 XML <;/col><col></col></row></rows> 基本上你需要添加开始和结束标签，然后用列标签和行分隔符替换列分隔符带有列和行标签
• .nodes 是 xml 数据类型的一种方法，当您想将 xml 数据类型实例分解为关系数据时非常有用"https://msdn.microsoft.com/en-us/library/ms188282.aspx
• as t(n) 告诉您最终将如何访问 XML 行和列.t 是表别名，n 是节点别名(有点像一行).所以 t.n.value() 得到一个特定的行
• COL[1] 表示获取行中的第一个 COL 标记，它是基于 1 的，因此 2 是下一个，然后是 3 等.
• CHAR(1) 是一个数据类型定义，表示 1 个字符，基于您的示例数据，每列只有 1 个字符.您可能注意到我在动态查询中创建了 VARCHAR(MAX)，因为如果数据类型未知，那么您将需要更大的灵活性.
• Format string as XML <rows><row><col></col><col></col></row><row><col></col><col></col></row></rows> Basically you need to add the beginning and ending tags then replace the column delimiter with the column tags and the row delimiter with both column and row tags
• .nodes is a method on the xml data type that "is useful when you want to shred an xml data type instance into relational data" https://msdn.microsoft.com/en-us/library/ms188282.aspx
• as t(n) tells you how you will end up accessing the XML row and column. t being the table alias and n being the node alias (kind of like a row). so t.n.value() gets a particular row
• COL[1] means get the first COL tag in the row it is 1 based so 2 is the next then 3 etc.
• CHAR(1) is a datatype definition meaning 1 character and was based on your example data having only 1 character per column. you may noticed I made it VARCHAR(MAX) in the dynamic query because if data type is unknown then you will want more flexibility.

或动态

DECLARE @str varchar(max)='A,B,C,D,E;F,G,H,I,J;K,L,M,N,O';
DECLARE @NumOfColumns INT
SET @NumOfColumns = (LEN(@str) - LEN(REPLACE(@str,',',''))) / (LEN(@str) - LEN(REPLACE(@str,';','')) + 1) + 1

DECLARE @xmlstr XML
SET @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)

DECLARE @ParameterDef NVARCHAR(MAX) = N'@XMLInputString xml'
DECLARE @SQL NVARCHAR(MAX) = 'SELECT '

DECLARE @i INT = 1

WHILE @i <= @NumOfColumns
BEGIN
    SET @SQL = @SQL + IIF(@i > 1,',','') + 't.n.value(''col[' + CAST(@i AS VARCHAR(10)) + ']'',''NVARCHAR(MAX)'') as Col' + CAST(@i AS VARCHAR(10))

    SET @i = @i + 1
END

SET @SQL = @SQL + ' FROM
    @XMLInputString.nodes (''/rows/row'') AS t(n)'

EXECUTE sp_executesql @SQL,@ParameterDef,@XMLInputString = @xmlstr

这篇关于在 SQL Server 中将字符串拆分为给定行分隔符和列分隔符的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！