如何仅使用字符串函数将分隔字符串解析为列( Server 2008 R2)？

Question

我希望将数据库字段中的分隔字符串解析为多个列。字符串可能有从0到7个组件，由一个特殊字符分隔(char(7)，在我的特殊情况下)。不会有超过七个组件；如果有，它们将被忽略，并在最后一个组件中包含在分隔符中。--我需要在不使用UDF或T-SQL.xml的情况下这样做--我不认为XML解析功能适合这一点，但我会考虑一种有效的解决方案。

这就留下了字符串操作函数。，因为我在Server 2008 R2中，string_split()函数不是一个选项。我对它的任何改进感兴趣。

create table #x (a int, delimited_value varchar(8000))
insert into #x values 
 (1, 'abc')
,(2, 'defgh' + char(7) + 'ij' + char(7) + 'klmnop')
,(3, '')
,(4, 'qr' + char(7) + 's' + char(7) + 't' + char(7) + 'u' + char(7) + 'v' + char(7) + 'w' + char(7) + 'xyz')
,(5, '012' + char(7) + char(7) + '3' + char(7))
,(6, char(7) + char(7) + '4567' + char(7) + char(7) + '89')

select a
,substring(delimited_value, 1, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) - 1) as component1
,substring(delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0) + 1, 8000), isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8001) - isnull(nullif(charindex(char(7), delimited_value), 0), 8000) - 1) as component2
,substring(delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0) + 1, 8000), isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8001) - isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) - 1) as component3
,substring(delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0) + 1, 8000), isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8001) - isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) - 1) as component4
,substring(delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0) + 1, 8000), isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8001) + 1 ), 0), 8001) - isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) - 1) as component5
,substring(delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0) + 1, 8000), isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8001) - isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) - 1) as component6
,substring(delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value, isnull(nullif(charindex(char(7), delimited_value), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0), 8000) + 1 ), 0) + 1, 8000), 8000) as component7
from #x

Answer 1

尽管有不使用函数的要求，我还是发布了一个使用它的解决方案。这是一个内联表值函数，速度非常快。除非使用CLR，否则您将不会在那里找到一个快速拆分器。您可以在这里找到文章和代码。http://www.sqlservercentral.com/articles/Tally+Table/72993/

如果你不喜欢这个，这里还有几个很好的选择。https://sqlperformance.com/2012/07/t-sql-queries/split-strings

使用Jeff拆分器(来自上面的第一个链接)，您的代码将非常简单。

select x.a
    , component1 = max(case when y.ItemNumber = 1 then y.Item end)
    , component2 = max(case when y.ItemNumber = 2 then y.Item end)
    , component3 = max(case when y.ItemNumber = 3 then y.Item end)
    , component4 = max(case when y.ItemNumber = 4 then y.Item end)
    , component5 = max(case when y.ItemNumber = 5 then y.Item end)
    , component6 = max(case when y.ItemNumber = 6 then y.Item end)
    , component7 = max(case when y.ItemNumber = 7 then y.Item end)
from #x x
cross apply dbo.DelimitedSplit8K(x.delimited_value, char(7)) y
group by x.a

Answer 2

我认为XML方法在这里是简单而有效的解决方案。

示例

Select A.a
      ,B.*
 From  #x A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace(delimited_value,char(7),'</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

返回

a   Pos1    Pos2    Pos3    Pos4    Pos5    Pos6    Pos7
1   abc     NULL    NULL    NULL    NULL    NULL    NULL
2   defgh   ij      klmnop  NULL    NULL    NULL    NULL
3           NULL    NULL    NULL    NULL    NULL    NULL
4   qr      s       t       u       v       w       xyz
5   012             3               NULL    NULL    NULL
6                   4567            89      NULL    NULL