SQL Split Tab Delimited Column

Question

I have a tab separated column in a DB that contains a header field and its related results. I need assistance with a query that will return that data in separate rows based on an IP column. Below is a sample of the data and also the desired results.

The below query yields this result.

DECLARE @Tmp TABLE (ID int Identity(1,1), IP nvarchar(255),Results NVARCHAR(max));
INSERT into @Tmp select top 1 ip, Results from MyTable where results like 'Package%'
select * from @Tmp

This is the desired result.

IP              Package      Installed Version          Required Version
10.48.8.28      nss          3.21.0-9.el7_2.x86_64      3.21.3-2.el7_3
10.48.8.28      nss-sysinit  3.21.0-9.el7_2.x86_64      3.21.3-2.el7_3
10.48.8.28      nss-tools    3.21.0-9.el7_2.x86_64      3.21.3-2.el7_3
10.48.8.28      nss-util     3.21.0-2.2.el7_2.x86_64    3.21.3-1.1.el7_3

This is the data as copied from the SQL result window to the clipboard.

ID  IP          Results
1   10.46.8.28  Package Installed Version   Required Version
                python  2.7.5-39.el7_2.x86_64   2.7.5-48.el7
                python-libs 2.7.5-39.el7_2.x86_64   2.7.5-48.el7

Can you post your sample data as something consumable? It is impossible to tell what you have going on there. Is that two columns? Where is the column separation? Why does only the first row have ip? — Sean Lange, Feb 15 '17 at 21:13
@SeanLange I have updated the post with a photo that hopefully adds more clarity. — Eddie D, Feb 15 '17 at 22:28

John Cappelletti · Accepted Answer · 2017-02-15T22:53:35.970

Create Sample Data

Declare @YourTable table (ID int,IP varchar(50),TabString varchar(max))
Insert Into @YourTable values
(1,'444.333.222.11','Package Installed Version  Required Version
nss 3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-sysinit 3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-tools   3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-util    3.21.0-2.2.el7_2.x86_64 3.21.3-1.1.el7_3')

Which looks like this

Option 1 (with parse functions)

Select A.ID
      ,A.IP
      ,Package             = Pos1
      ,[Installed Version] = Pos2
      ,[Required Version]  = Pos3
 From  @YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.TabString,char(13)) B
 Cross Apply [dbo].[udf-Str-Parse-Row](B.RetVal,char(9)) C
 Where B.RetVal is not null and B.RetSeq>1

Returns

Option 2 (without parse function)

Select A.ID
      ,A.IP
      ,Package             = Pos1
      ,[Installed Version] = Pos2
      ,[Required Version]  = Pos3
 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 replace(A.TabString,char(13),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
                Cross Apply x.nodes('x') AS B(i)
             ) B
 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)')))
                From  (Select Cast('<x>' + replace((Select replace(B.RetVal,char(9),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as X 
             ) C
 Where B.RetVal is not null and B.RetSeq>1

Returns

The Parse Functions if Interested

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((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    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)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From  (Select Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
)
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John <test> Cappelletti',' ')
--Select * from [dbo].[udf-Str-Parse-Row]('A&B;C;D;E, F;<x>',';')

John thank you this solution puts me in the ballpark but it is a bit off based on the original data in the DB. I have attached a picture of the actual result yielded by the DB. — Eddie D, Feb 15 '17 at 22:27
@EddieD Easy fix. Give me a moment. The image is very helpful — John Cappelletti, Feb 15 '17 at 22:38
@EddieD Tweaked the WHERE to exclude the 1st row regardless of header — John Cappelletti, Feb 15 '17 at 22:54

score 1 · Answer 2 · answered Feb 17 '17 at 11:40

If you're using SQL Server 2016 you can use STRING_SPLIT()

According to Aaron Bertrand it's the fastest way. See this https://sqlperformance.com/2016/03/sql-server-2016/string-split

Declare @YourTable table (ID int,IP varchar(50),TabString varchar(max));
Insert Into @YourTable values
(1,'444.333.222.11','Package    Installed Version   Required Version
nss 3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-sysinit 3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-tools   3.21.0-9.el7_2.x86_64   3.21.3-2.el7_3
nss-util    3.21.0-2.2.el7_2.x86_64 3.21.3-1.1.el7_3');


;WITH CTE_Lines AS
(
select ID, IP, a.ROWID , a.Lines from @YourTable
    CROSS APPLY (
        SELECT 
           ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ROWID, value Lines
         FROM STRING_SPLIT(TabString, CHAR(13)) --Splits by new lines
    ) a
), CTE_Columns AS
(
SELECT ID, IP, ROWID, b.ColsID, b.[Cols] FROM CTE_Lines
    CROSS APPLY (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ColsID,
        value [Cols] 
        FROM STRING_SPLIT(Lines, CHAR(9)) --Splits by tabs
    ) b 
    WHERE ROWID <> 1 --Removes the headers
)

SELECT ID, IP,  [1] Package, [2] [Installed Version],[3] [Required Version]  FROM 
(
    SELECT ID, IP, ROWID, ColsID, [Cols]  FROM CTE_Columns 
) a
PIVOT (MAX(a.Cols) FOR ColsId in ([1],[2],[3])) as pvt

Your example didn't quite work for me, but was useful pointer for me. — Sir Swears-a-lot, Jan 04 '18 at 20:54

SQL Split Tab Delimited Column

2 Answers2

Linked