SQL Server 2014:将两个逗号分隔的字符串转换为两列 [英] SQL Server 2014 : Convert two comma separated string into two columns

问题描述

我有两个逗号分隔的字符串，需要将其转换为具有基于索引同步的两列的临时表.

I have two comma-separated string which needs to be converted into a temptable with two columns synchronized based on the index.

如果输入字符串如下

a = 'abc,def,ghi'
b = 'aaa,bbb,ccc'

那么输出应该是

column1 | column2
------------------
abc     | aaa
def     | bbb
ghi     | ccc

假设我有函数 fnConvertCommaSeparatedStringToColumn，它接受逗号分隔的字符串和分隔符作为参数并返回一个带有值的列.我在两个字符串上使用它并获取两列来验证两边的计数是否相同.但是最好将它们放在一个临时表中.我该怎么做?

Let us say I have function fnConvertCommaSeparatedStringToColumn which takes in comma-separated string and delimiter as a parameter and returns a column with values. I use this on both strings and get two columns to verify if the count is the same on both sides. But it would be nice two have them in a single temp table. How can i do that?

推荐答案

我建议获得一个(基于集合的)函数，该函数可以根据分隔符拆分字符串，同时返回序数位置.例如 DelimitedSplit8k_LEAD.然后你可以简单地分割值，并在序数位置JOIN:

I would suggest getting a (set based) function that can split a string, based on a delimiter, that returns the ordinal position as well. For example DelimitedSplit8k_LEAD. Then you can trivially split the value, and JOIN on the ordinal position:

DECLARE @a varchar(100) = 'abc,def,ghi';
DECLARE @b varchar(100) = 'aaa,bbb,ccc';

SELECT A.Item AS A,
       B.Item AS B
FROM dbo.delimitedsplit8k_lead(@a,',') A
     FULL OUTER JOIN dbo.delimitedsplit8k_lead(@a,',') B ON A.ItemNumber = B.ItemNumber;

db<>fiddle

我使用 FULL OUTER JOIN 然后如果任一列具有 NULL 值，您就知道 2 个分隔列表没有相同数量的分隔值.

I use a FULL OUTER JOIN and then if either column has a NULL value you know that the 2 delimited lists don't have the same number of delimited values.

这篇关于SQL Server 2014:将两个逗号分隔的字符串转换为两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！