SQL Server:将字符串拆分为行 [英] SQL Server : Split string to row

问题描述

如何从下方翻转数据:

CODE COMBINATION   USER
1111.111.11.0      KEN; JIMMY
666.778.0.99       KEN
888.66.77.99       LIM(JIM); JIMMY

到

CODE COMBINATION   USER
1111.111.11.0      KEN
1111.111.11.0      JIMMY
666.778.0.99       KEN
888.66.77.99       LIM(JIM)
888.66.77.99       JIMMY

我知道在 SQL Server 2016 中这可以通过拆分字符串函数来完成，但我的产品是 SQL Server 2014.

I know in SQL Server 2016 this can be done by split string function, but my production is SQL Server 2014.

推荐答案

使用此 TVF，您可以提供要拆分的字符串和定界符.此外，您还可以获得序列号，这对于二次处理非常有用.

With this TVF, you can supply the string to be split and delimiter. Furthermore, you get the sequence number which can be very useful for secondary processing.

Select [CODE COMBINATION]
      ,[USER] = B.RetVal
 From  YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.[USER],';') B

退货

解析 UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    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(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

<小时>

现在，另一个选择是解析行 UDF.注意我们在一行中返回解析后的字符串.目前有9个职位，但很容易扩大或收缩.

---

Now, another option is the Parse-Row UDF. Notice we return the parsed string in one row. Currently 9 positions, but it is easy to expand or contract.

Select [CODE COMBINATION]
      ,B.*
 From  YourTable A
 Cross Apply [dbo].[udf-Str-Parse-Row](A.[USER],';') B

退货

解析行 UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = xDim.value('/x[1]','varchar(max)')
          ,Pos2 = xDim.value('/x[2]','varchar(max)')
          ,Pos3 = xDim.value('/x[3]','varchar(max)')
          ,Pos4 = xDim.value('/x[4]','varchar(max)')
          ,Pos5 = xDim.value('/x[5]','varchar(max)')
          ,Pos6 = xDim.value('/x[6]','varchar(max)')
          ,Pos7 = xDim.value('/x[7]','varchar(max)')
          ,Pos8 = xDim.value('/x[8]','varchar(max)')
          ,Pos9 = xDim.value('/x[9]','varchar(max)')
     From (Select Cast('<x>' + Replace(@String,@Delimiter,'</x><x>')+'</x>' as XML) as xDim) A
)
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')

这篇关于SQL Server:将字符串拆分为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！