SQL Server中如何拆分多个列中的字符上的字符串
我需要拆分由波浪号(〜)字符分隔的列中的值,但对于单个行中的多个列。我可以使用XML拆分一列,但是我在解决如何拆分多列时遇到问题。SQL Server中如何拆分多个列中的字符上的字符串
这是一个行目前看起来是这样的:
Column1 Column2 Column3 Column4
[JJ2222] [~BLUE~BROWN~BLACK] [~BB1234~BC2345~BD3456] [~BLUE, BABY (BB1234)~BROWN, COW (BC2345)~BLACK, DOG (BD3456)]
拆分后,我期待与锚(JJ2222)排列所有的值多行:
Column1 Column2 Column3 Column4
JJ2222 BLUE BB1234 BLUE, BABY (BB1234)
JJ2222 BROWN BC2345 BROWN, COW (BC2345)
JJ2222 BLACK BD3456 BLACK, DOG (BD3456)
在我的知识有限,我可能会创建3个独立的查询,将每列分别加载到临时表中,然后将每个表加入到Column1中,但我希望有一种方法可以在一个查询中完成。
有很多关于如何拆分字符串的例子。这里的技巧是链接或连接序列。
如果打开到UDF
例
Select A.Column1
,B.*
From YourTable A
Cross Apply (
Select Column2=B1.RetVal
,Column3=B2.RetVal
,Column4=B3.RetVal
From [dbo].[tvf-Str-Parse](A.Column2,'~') B1
Join [dbo].[tvf-Str-Parse](A.Column3,'~') B2 on B1.RetSeq=B2.RetSeq
Join [dbo].[tvf-Str-Parse](A.Column4,'~') B3 on B1.RetSeq=B3.RetSeq
Where B1.RetVal is not null
and B2.RetVal is not null
and B3.RetVal is not null
) B
返回
Column1 Column2 Column3 Column4
JJ2222 BLUE BB1234 BLUE, BABY (BB1234)
JJ2222 BROWN BC2345 BROWN, COW (BC2345)
JJ2222 BLACK BD3456 BLACK, DOG (BD3456)
的UDF如果有意
CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[tvf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[tvf-Str-Parse]('John Cappelletti was here',' ')
谢谢John!你的脚本工作完美。它完全符合我的要求。 –
@RonnieJ乐于助人 –
这是我非常喜欢Jeff Moden的分配器的那个时代。 http://www.sqlservercentral.com/articles/Tally+Table/72993/它是我所知道的唯一一个分隔符,它返回每个元素的序数位置,而不是循环。正如评论中所述,不存储这样的数据是迄今为止最好的选择,如果可能的话。要解决这个问题,你必须拆分每一列。
这件事应该适合你。它适用于您的示例数据。
declare @Something table
(
Column1 varchar(20)
, Column2 varchar(50)
, Column3 varchar(50)
, Column4 varchar(500)
)
insert @Something
select 'JJ2222', '~BLUE~BROWN~BLACK', '~BB1234~BC2345~BD3456', '~BLUE, BABY (BB1234)~BROWN, COW (BC2345)~BLACK, DOG (BD3456)'
;
select s.Column1
, c2.Item
, c3.Item
, c4.Item
from @Something s
cross apply dbo.DelimitedSplit8K(s.Column2, '~') c2
cross apply dbo.DelimitedSplit8K(s.Column3, '~') c3
cross apply dbo.DelimitedSplit8K(s.Column4, '~') c4
where c2.Item > '' --this eliminates an empty row because you have the delimiter at the beginning of the string.
and c2.ItemNumber = c3.ItemNumber
and c2.ItemNumber = c4.ItemNumber
由于拧紧已经在那里,它不会帮助它成为一个不同的(稍小一点)。你需要的是将这些数据分成不同的表格。
除第1列之外的所有内容都需要成为另一个表的外键。
对于原始表格中列中每个项目的组合,可以使用适当的附加表格。每个这些表的样子:
CombinationId,价值
当你有例如:
[~BLUE~BROWN~BLACK]
你把它分解成附加表组合2所示:
CombinationId Value
1 BLUE
1 BROWN
1 BLACK
你做这为原始表的所有列。当你为每一列逐行处理原始表格时,你检查你创建CombinationsX的新表格是否已经有组合,就像共享相同的ID一样。如果是这样,你把这个Id放在原始表中。如果没有,则将该组合添加到新索引中,并将该数字作为外键放入原始表中。
您现在已将您的数据分成多个表并使其可用。
你从现在开始想做什么不同,但至少现在你可以将它改造成不同的东西。至少现在你可以查询数据,搜索,索引等等。当你不用垃圾填充它时,你期望从数据库中提供的东西;)
有几十个重复说相同:*避免*首先插入这些值。加载数据时拆分字符串要容易得多。您不能索引或搜索这些列。 SQL Server在2016年添加了一个'STRING_SPLIT'命令,可用于清理这些条目 –
Panagiotis - 感谢您的建议。是的,我熟悉2016年添加的String_Split函数,但不幸的是,此版本是2012. –