分隔符上的 T-SQL 拆分 [英] T-SQL split on delimiter
本文介绍了分隔符上的 T-SQL 拆分的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在处理采用以下格式的员工层次结构字符串.这些编号代表employeeID 编号以及它们在公司内部的结构，从而能够遵循管理链.
123|456|789|012|345|320我正在尝试获取此数据字符串并将其转换为临时表，以便我可以将每个 ID 用作它们自己的值.
我尝试制作一个拆分字符串的函数:
ALTER FUNCTION [dbo].[SplitString](@String NVARCHAR(4000),@Delimiter NCHAR(1))退货表作为返回(with Split(stpos, endpos) AS(SELECT 0 AS stpos, CHARINDEX(@Delimiter, @String) AS endpos联合所有SELECT endpos + 1, CHARINDEX(@Delimiter, @String, endpos+1)从拆分WHERE 端点 >0)选择'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),'数据' = SUBSTRING(@String, stpos, COALESCE(NULLIF(endpos, 0), LEN(@String) + 1))从分裂)结果如下:
Id 数据-------------------1 1232 456|78933 7893|012|345|4 012|345|3205 345|3206 320有没有更好的方法来解决这个问题，可能根本不需要一个函数，还是需要它来实现?
解决方案
没有解析函数
声明@YourTable 表 (ID int,IDList varchar(Max))插入@YourTable 值(1,'123|456|789|012|345|320'),(2,'123|456')选择A.ID,B.*来自@YourTable A交叉申请 (选择 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 A.IDList as [*] For XML Path('')),'|','</x><x>')+'</x>' 作为 xml).query('.')) 作为 A交叉应用 x.nodes('x') AS B(i)) 乙退货
ID RetSeq RetVal1 1 1231 2 4561 3 7891 4 0121 5 3451 6 3202 1 1232 2 456<块引用>
或使用 SUPER DUPER Parse(下面列出的原始来源/一些调整)
选择A.ID,B.*来自@YourTable A交叉应用 [dbo].[udf-Str-Parse-8K](A.IDList,'|') B将返回与上面相同的内容
CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(10))退货表作为返回 (with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))) N(N)),cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1d) 一种 )，cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),cte4(N,L) As (选择 S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) 从 cte3 S)选择 RetSeq = Row_Number() over (Order By A.N),RetVal = Substring(@String, A.N, A.L)从 cte4 A);--原始来源 http://www.sqlservercentral.com/articles/Tally+Table/72993/-- 比 str-Parse 快得多，但仅限于 8K--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')<块引用>
编辑 - 独立
声明@String varchar(max) = '123|456|789|012|345|320'声明@Delim varchar(10) = '|'选择 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 @String as [*] For XML Path('')),@Delim,'</x><x>')+'</x>' 作为 xml).query('.')) 作为 A交叉应用 x.nodes('x') AS B(i)I am working with an employee hierarchy string that is in the format of the following. These number represent employeeID numbers and how the are structured within the company, thus being able to follow the chain of management.
123|456|789|012|345|320
I am trying to take this string of data and turn it into a temp table so I can work with each of the ID's as their own value.
I tried making a function to split the string:
ALTER FUNCTION [dbo].[SplitString]
(@String NVARCHAR(4000),
@Delimiter NCHAR(1))
RETURNS TABLE
AS
RETURN
(WITH Split(stpos, endpos) AS
(
SELECT 0 AS stpos, CHARINDEX(@Delimiter, @String) AS endpos
UNION ALL
SELECT endpos + 1, CHARINDEX(@Delimiter, @String, endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT
'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String, stpos, COALESCE(NULLIF(endpos, 0), LEN(@String) + 1))
FROM
Split
)
This however resulted in the following:
Id Data
-------------------
1 123
2 456|7893
3 7893|012|345|
4 012|345|320
5 345|320
6 320
Is there a better way to approach this, maybe not needing a function at all or will it be required to achieve this?
解决方案
Without a Parse Function
Declare @YourTable table (ID int,IDList varchar(Max))
Insert Into @YourTable values
(1,'123|456|789|012|345|320'),
(2,'123|456')
Select A.ID
,B.*
From @YourTable A
Cross Apply (
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 A.IDList as [*] For XML Path('')),'|','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
) B
Returns
ID RetSeq RetVal
1 1 123
1 2 456
1 3 789
1 4 012
1 5 345
1 6 320
2 1 123
2 2 456
OR with the SUPER DUPER Parse (orig source listed below / couple of tweaks)
Select A.ID
,B.*
From @YourTable A
Cross Apply [dbo].[udf-Str-Parse-8K](A.IDList,'|') B
Would Return the same as above
CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By A.N)
,RetVal = Substring(@String, A.N, A.L)
From cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')
Edit - Stand Alone
Declare @String varchar(max) = '123|456|789|012|345|320'
Declare @Delim varchar(10) = '|'
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 @String as [*] For XML Path('')),@Delim,'</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
这篇关于分隔符上的 T-SQL 拆分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
