分割分号分隔符SQL到行 [英] split semicolon delimiter SQL to rows
问题描述
只是想要求帮助。
我试图用分号作为分隔符拆分分隔的值。
逗号不能替换为分号,因为值有逗号。
Just want to ask for help. I'm trying to split delimited values with a semicolon as a delimiter. Comma cannot be replaced to the semicolon since there are values that have comma.
ID Value
1 | A&B;C;D;E, F
转换为:
ID Value
1 A&B
1 C
1 D
1 E, F
我尝试调整我在线但无法成功的SQL脚本
I tried tweaking the SQL scripts that i got online but to no success
SELECT F1.ID,
O.splitdata
FROM
(
SELECT OldID,
cast('<X>'+replace((SELECT ColumnName + '' FOR XML PATH('')),';','</X><X>')+'</X>' as XML) as xmlfilter from TableName F
)F1
CROSS APPLY
(
SELECT fdata.D.value('.','varchar(max)') as splitdata
FROM f1.xmlfilter.nodes('X') as fdata(D)) O
我的一些列,但如果列有特殊或非法字符,它会输出此错误:
It works for some of my columns but if the columns have special or Illegal characters it outputs this error:
Msg 9411, Level 16, State 1, Line 2
XML parsing: line 1, character 16, semicolon expected
谢谢! / p>
Thanks!
推荐答案
如果您不喜欢某个功能,或者您没有创建新功能的权限,可以使用相当快的XML方法。在你的情况下,它需要一些额外的努力来获得这个XML安全(由于特殊字符和;
作为分隔符):
If you do not like a function, or if you do not have the rights to create a new function, you can use the quite fast XML approach. In your case it needs some extra effort to get this XML-safe (due to special characters and the ;
as delimiter):
Declare @Dummy table (ID int, SomeTextToSplit varchar(max))
Insert Into @Dummy values
(1,'A&B;C;D;E, F')
,(2,'"C" & "D";<C>;D;E, F');
DECLARE @Delimiter VARCHAR(10)=';';
WITH Casted AS
(
SELECT *
,CAST('<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,'§§Split$me$here§§') AS [*] FOR XML PATH('')),'§§Split$me$here§§','</x><x>') + '</x>' AS XML) AS SplitMe
FROM @Dummy
)
SELECT Casted.*
,x.value('.','nvarchar(max)') AS Part
FROM Casted
CROSS APPLY SplitMe.nodes('/x') AS A(x)
结果
1 A&B
1 C
1 D
1 E, F
2 "C" & "D"
2 <C>
2 D
2 E, F
这篇关于分割分号分隔符SQL到行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!