SQL Server : Split string to row

问题:

How to turn data from below:

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

To

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

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

回答1:

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

Returns

The Parse 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',' ')

---

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

Returns

The Parse Row 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',' ')

回答2:

You need to use a UDF for splitting it on each row

CREATE FUNCTION [DBO].[FN_SPLIT_STR_TO_COL] (@T AS VARCHAR(4000) )
RETURNS
 @RESULT TABLE(VALUE VARCHAR(250))
AS
BEGIN
     SET @T= @T+';'
       ;WITH MYCTE(START,[END]) AS(

    SELECT 1 AS START,CHARINDEX(';',@T,1) AS [END]
    UNION ALL
    SELECT [END]+1 AS START,CHARINDEX(';',@T,[END]+1)AS [END] 
    FROM MYCTE WHERE [END]<LEN(@T)
    )
    INSERT INTO @RESULT 
    SELECT SUBSTRING(@T,START,[END]-START) NAME FROM MYCTE;

      RETURN 
END

Now query on your table by calling above function with CROSS APPLY

  SELECT [CodeCombination],FN_RS.VALUE FROM TABLE1
  CROSS APPLY
 (SELECT * FROM [DBO].[FN_SPLIT_STR_TO_COL] (User))           
  AS FN_RS

回答3:

If your [USER] column only has one semicolon you don't need a "split string" function at all; you could use CROSS APPLY like this:

-- Your Sample data 
DECLARE @table TABLE (CODE_COMBINATION varchar(30), [USER] varchar(100));
INSERT @table
VALUES ('1111.111.11.0', 'KEN; JIMMY'), ('666.778.0.99', 'XKEN'),
       ('888.66.77.99','LIM(JIM); JIMMY');

-- Solution using only CROSS APPLY
SELECT CODE_COMBINATION, [USER] = LTRIM(s.s)
FROM @table t
CROSS APPLY (VALUES (CHARINDEX(';',t.[USER]))) d(d)
CROSS APPLY 
(
  SELECT SUBSTRING(t.[USER], 1, ISNULL(NULLIF(d.d,0),1001)-1)
  UNION ALL
  SELECT SUBSTRING(t.[USER], d.d+1, 1000)
  WHERE d.d > 0
) s(s);

If you do need a pre SQL Server 2016 "split string" function I would strongly suggest using Jeff Moden's DelimitedSplit8k or Eirikur Eiriksson's DelimitedSplit8K_LEAD. Both of these will outperform an XML-based or recursice CTE "split string" function.