我有一个以下格式的字符串：
A:B:C;J:K;P:L:J;
我想在冒号（:）后拆分字符串，并在分号（;）后开始新行。 有人能帮我查询吗。
输出示例：
A B C
J K
P L J
4个回答
9
尝试这个方案 -
解决方案 #1：
DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'
SELECT *
FROM (
SELECT token = t.c.value('.', 'VARCHAR(100)')
FROM
(
SELECT xmls = CAST('<t>' +
REPLACE(
REPLACE(@t, ':', ' '),
';',
'</t><t>') + '</t>' AS XML)
) r
CROSS APPLY xmls.nodes('/t') AS t(c)
) t
WHERE t.token != ''
输出：
----------
A B C
J K
P L J
解决方案 #2：
DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'
PRINT REPLACE(REPLACE(@t, ':', ' '), ';', CHAR(13) + CHAR(13))
输出：
A B C
J K
P L J
解决方案 #3：
DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'
SELECT [1], [2], [3]
FROM (
SELECT
t2.id
, t2.name
, rn2 = ROW_NUMBER() OVER (PARTITION BY t2.id ORDER BY 1/0)
FROM (
SELECT
id = t.c.value('@n', 'INT')
, name = t.c.value('@s', 'CHAR(1)')
FROM (
SELECT x = CAST('<t s = "' +
REPLACE(token + ':', ':', '" n = "' + CAST(rn AS VARCHAR(10))
+ '" /><t s = "') + '" />' AS XML)
FROM (
SELECT
token = t.c.value('.', 'VARCHAR(100)')
, rn = ROW_NUMBER() OVER (ORDER BY 1/0)
FROM (
SELECT x = CAST('<t>' + REPLACE(@t, ';', '</t><t>') + '</t>' AS XML)
) r
CROSS APPLY x.nodes('/t') t(c)
) t
) d
CROSS APPLY x.nodes('/t') t(c)
) t2
WHERE t2.name != ''
) t3
PIVOT (
MAX(name) FOR rn2 IN ([1], [2], [3])
) p
输出：
1 2 3
---- ---- ----
A B C
J K NULL
P L J
- Devart
3
@rahul aggarwal，请问您能告诉我我的答案哪里有问题吗？ - Devart
答案是正确的，但在上面的解决方案中，输出显示在3个不同的列中。感谢您提供的解决方案。 - rahul aggarwal
@Devart 问题是如何将解决方案2的输出分成单独的列？有什么办法吗？ - Przemyslaw Remin
4
我不确定是否理解正确，但如果您需要将数据作为三列行集提供：
declare @str nvarchar(max)
set @str = 'A:B:C;J:K;P:L:J;'
select p.[1] as Column1, p.[2] as Column2, p.[3] as Column3
from (
select T.c.value('.', 'nvarchar(200)') [row], row_number() over (order by @@spid) rn1
from (select cast('<r>' + replace(@str, ';', '</r><r>') + '</r>' as xml) xmlRows) [rows]
cross apply xmlRows.nodes('/r') as T(c)
where T.c.value('.', 'nvarchar(200)') != ''
) t1
cross apply (
select NullIf(T.c.value('.', 'nvarchar(200)'), '') row2,
row_number() over (order by @@spid) rn
from (select cast('<r>' + replace(t1.row, ':', '</r><r>') + '</r>' as xml) xmlRows) [rows]
cross apply xmlRows.nodes('/r') as T(c)
) t2
pivot (max(t2.row2) for t2.rn in ([1], [2], [3])) p
order by p.rn1
输出
Column1 Column2 Column3
-------- -------- -------
A B C
J K NULL
P L J
- i-one
1
这个解决方案不是通用的。它不能将任何字符串按列分隔符和行分隔符拆分成表格。它无法处理例如这个字符串A:B:C:X;J:K:X;P:L:J:X;(添加了第四列)。 - Przemyslaw Remin
1
SQL中没有内置的split函数，但是你可以通过运行像这样的语句来自己创建该函数。这种解决方案可以重复使用于其他需要执行多部分拆分的情况。
CREATE FUNCTION dbo.Split
(
@TextToSplit nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@TextToSplit)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@TextToSplit,1,Charindex(@SplitOn,@TextToSplit)- 1)))
Set @RowData = Substring(@TextToSplit,Charindex(@SplitOn,@TextToSplit)+1,len(@TextToSplit))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@TextToSplit))
Return
END
然后您可以像这样运行它：
Declare @SplitThis as varchar(2000) = 'A:B:C;J:K;P:L:J'
DECLARE @Val as varchar(500)
DECLARE MyCursor CURSOR FAST_FORWARD FOR
select data from split(@SplitThis,';')
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @Val
WHILE @@FETCH_STATUS = 0
BEGIN
select(
select ' ' + data
from split(@Val,':')
for xml Path(''),type).value('.','varchar(100)') as cat
FETCH NEXT FROM MyCursor INTO @Val
END
CLOSE MyCursor
DEALLOCATE MyCursor
- Brian
1
祝你的项目好运。 - Brian
1
对于较大的字符串，我发现UDF循环方法比xml方法更有效。
create Function [Split_Text_Into_Table_Multi_Column] (
@Row_Delimit_Char Varchar(5)
,@Column_Delimit_Char Varchar(5)
,@Text_To_Split Varchar(Max) )
Returns
@Output_Table Table (
row_id Int Identity (1, 1)
,column_1 Varchar(Max) Collate Latin1_General_CS_AS
,column_2 Varchar(Max) Collate Latin1_General_CS_AS
,column_3 Varchar(Max) Collate Latin1_General_CS_AS
)
As
Begin
Declare
@Column_Delimit_Index_1 Int
,@Column_Delimit_Index_2 Int
,@String Varchar(Max)
,@String_1 Varchar(Max)
,@String_2 Varchar(Max)
,@String_3 Varchar(Max)
,@String_Len Int
,@Delimit_Len TinyInt
Declare
@Last_Delimit_Index Int = 0
,@Next_Delimit_Index Int = 1
--always treat new line as delimiter
Select
@Text_To_Split = Replace(Replace(@Text_To_Split, Char(10), @Row_Delimit_Char), Char(13), @Row_Delimit_Char)
--begin loop while next delimiter found
While @Next_Delimit_Index > 0
Begin
--find next delimiter
Select
@Next_Delimit_Index = CharIndex(@Row_Delimit_Char, @Text_To_Split, @Last_Delimit_Index + 1)
--get value to insert
Select
@String = Ltrim(Rtrim(Substring(@Text_To_Split, @Last_Delimit_Index + 1, Case
When @Next_Delimit_Index = 0 Then Len(@Text_To_Split) - @Last_Delimit_Index
Else @Next_Delimit_Index - @Last_Delimit_Index - 1
End)))
--Insert Non blank strings only
If @String <> ''
Begin
Select
@Delimit_Len = Len(@Column_Delimit_Char)
Select
@String_Len = DataLength(@String)/*includes trailing spaces*/
,@Column_Delimit_Index_1 = 1
,@Column_Delimit_Index_2 = IsNull(NullIf(CharIndex(@Column_Delimit_Char, @String, @Column_Delimit_Index_1), 0), @String_Len + 1)
,@String_1 =
Case
When @Column_Delimit_Index_2 <= @Column_Delimit_Index_1 Or
@Column_Delimit_Index_2 - @Column_Delimit_Index_1 = 0 Then Null
Else Substring(@String, @Column_Delimit_Index_1, @Column_Delimit_Index_2 - @Column_Delimit_Index_1)
End
,@Column_Delimit_Index_1 = @Column_Delimit_Index_2 + @Delimit_Len
,@Column_Delimit_Index_2 = IsNull(NullIf(CharIndex(@Column_Delimit_Char, @String, @Column_Delimit_Index_1), 0), @String_Len + 1)
,@String_2 =
Case
When @Column_Delimit_Index_2 <= @Column_Delimit_Index_1 Or
@Column_Delimit_Index_2 - @Column_Delimit_Index_1 = 0 Then Null
Else Substring(@String, @Column_Delimit_Index_1, @Column_Delimit_Index_2 - @Column_Delimit_Index_1)
End
,@Column_Delimit_Index_1 = @Column_Delimit_Index_2 + @Delimit_Len
,@Column_Delimit_Index_2 = IsNull(NullIf(CharIndex(@Column_Delimit_Char, @String, @Column_Delimit_Index_1), 0), @String_Len + 1)
,@String_3 =
Case
When @Column_Delimit_Index_2 <= @Column_Delimit_Index_1 Or
@Column_Delimit_Index_2 - @Column_Delimit_Index_1 = 0 Then Null
Else Substring(@String, @Column_Delimit_Index_1, @Column_Delimit_Index_2 - @Column_Delimit_Index_1)
End
Insert Into
@Output_Table (
column_1
,column_2
,column_3)
Values
(
@String_1
,@String_2
,@String_3)
End
--set last last char index
Select
@Last_Delimit_Index = @Next_Delimit_Index
End
Return
End
--sample output
Select *
from [Split_Text_Into_Table_Multi_Column] (',',' ','
917064578 70
917581826 66
917635915 66
917663205 66
918320481 66
918752247 ER
918892315 70
919162748 52
919169913 JS
919169927 70
919187495 52
919207137 52
919226194 70
919252530 52
919252544 KG
919269942 52
919269957 KG
919269961 47
')
输出
row_id|column_1|column_2|column_3
1|917064578|70|NULL
2|917581826|66|NULL
3|917635915|66|NULL
4|917663205|66|NULL
5|918320481|66|NULL
6|918752247|ER|NULL
7|918892315|70|NULL
8|919162748|52|NULL
9|919169913|JS|NULL
10|919169927|70|NULL
11|919187495|52|NULL
12|919207137|52|NULL
13|919226194|70|NULL
14|919252530|52|NULL
15|919252544|KG|NULL
16|919269942|52|NULL
17|919269957|KG|NULL
18|919269961|47|NULL
- user1403869
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接