SQL Server 2008 T-SQL UDF Split() 定制

Question

我正在使用 SQL Ser 2008，并且有一个只有一列数据的大表。数据是一个随机字符串，一致性非常差。示例：名称帐户 445566 0010020056893010445478008 AFD 369。我一直在使用 stackoverflow 用户建议的 split 函数。它工作得很好，但该函数将分割字符串分配到一列中。我需要一行单独的列。目前的结果是 1col，其中包含值 Name、Account、445566...，但我正在寻找的结果是 col1 Name、col2 Account、col3 445566... 如果有人能够提供有关如何定制此脚本或其用法以获得所需结果的一些见解，我们将不胜感激。

CREATE FUNCTION [dbo].[Split] 
(    
 @String varchar(max) 
,@Delimiter char 
) 
RETURNS @Results table 
( 
 Ordinal int 
,StringValue varchar(max) 
) 
as 
begin 

set @String = isnull(@String,'') 
set @Delimiter = isnull(@Delimiter,'') 

declare 
 @TempString varchar(max) = @String 
,@Ordinal int = 0 
,@CharIndex int = 0 

set @CharIndex = charindex(@Delimiter, @TempString) 
while @CharIndex != 0 begin      
    set @Ordinal += 1        
    insert @Results values 
    ( 
     @Ordinal 
    ,substring(@TempString, 0, @CharIndex) 
    )        
    set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)      
    set @CharIndex = charindex(@Delimiter, @TempString) 
end 

if @TempString != '' begin 
    set @Ordinal += 1  
    insert @Results values 
    ( 
     @Ordinal 
    ,@TempString 
    ) 
end 

return 
end 

--The usage:
SELECT    
* 
FROM    
mytable M    
CROSS APPLY    
[dbo].[Split] (M.TheColumn, ' ') S
Where rtrim(s.StringValue) != '' 

Answer 1

如果您知道字符串中有 6 列，则可以使用如下所示的拆分函数，当然也可以将该函数修改为您想要的任意列数。函数不能返回动态数量的列。

create function dbo.Split6(@String varchar(max), @Delimiter char(1)) 
returns table as return
(
  select
    substring(T.Col, 1, S1.Pos-1) as Col1,
    substring(T.Col, S1.Pos+1, S2.Pos-S1.Pos-1) as Col2,
    substring(T.Col, S2.Pos+1, S3.Pos-S2.Pos-1) as Col3,
    substring(T.Col, S3.Pos+1, S4.Pos-S3.Pos-1) as Col4,
    substring(T.Col, S4.Pos+1, S5.Pos-S4.Pos-1) as Col5,
    substring(T.Col, S5.Pos+1, S6.Pos-S5.Pos-1) as Col6
  from (select @String+replicate(@Delimiter, 6)) as T(Col)
    cross apply (select charindex(@Delimiter, T.Col, 1)) as S1(Pos)
    cross apply (select charindex(@Delimiter, T.Col, S1.Pos+1)) as S2(Pos)
    cross apply (select charindex(@Delimiter, T.Col, S2.Pos+1)) as S3(Pos)
    cross apply (select charindex(@Delimiter, T.Col, S3.Pos+1)) as S4(Pos)
    cross apply (select charindex(@Delimiter, T.Col, S4.Pos+1)) as S5(Pos)
    cross apply (select charindex(@Delimiter, T.Col, S5.Pos+1)) as S6(Pos)
)

测试：

declare @T table (Col varchar(100))

insert into @T values
 ('Name Account 445566 0010020056893010445478008 AFD 369'),
 (''),
 ('1 2'),
 ('1  3')

select S.Col1, S.Col2, S.Col3, S.Col4, S.Col5, S.Col6
from @T as T
  cross apply
    dbo.Split6(T.Col, ' ') as S

结果：

Col1  Col2     Col3    Col4                       Col5  Col6
----  -------  ------  -------------------------  ----  ----
Name  Account  445566  0010020056893010445478008  AFD   369

1     2             
1              3            

Answer 2

您可以尝试使用 PIVOT。

http://msdn.microsoft.com/en-us/library/ms177410.aspx