Delimiting string/varchar into rows to insert into #temp table

Question

I am trying to create a temp table and insert rows from a long string '!*|*!'.

This is what I have:

            Declare @value varchar(max) = '70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836092!|!???!*|*!70!|!05/28/2017!|!05/25/2017!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836093!|!???!*|*!70!|!none!|!none!|!ALLERGAN USA, INC.[A475]!|!Y!|!N!|!GERIMEDB0A!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836094!|!???!*|*!' 
            DECLARE @my1 VARCHAR(500)
            DECLARE @my2 VARCHAR(500)
            DECLARE @my3 VARCHAR(500)
            DECLARE @my4 VARCHAR(500)
            DECLARE @my5 VARCHAR(500)
            DECLARE @my6 VARCHAR(500)
            DECLARE @my7 VARCHAR(500)
            DECLARE @my8 VARCHAR(500)

            SELECT @my1 = ltrim(rtrim(xDim.value('/x[1]', 'varchar(max)')))
                ,@my2 = ltrim(rtrim(xDim.value('/x[2]', 'varchar(max)')))
                ,@my3 = ltrim(rtrim(xDim.value('/x[3]', 'varchar(max)')))
                ,@my4 = ltrim(rtrim(xDim.value('/x[4]', 'varchar(max)')))
                ,@my5 = ltrim(rtrim(xDim.value('/x[5]', 'varchar(max)')))
                ,@my6 = ltrim(rtrim(xDim.value('/x[6]', 'varchar(max)')))
                ,@my7 = ltrim(rtrim(xDim.value('/x[7]', 'varchar(max)')))
                ,@my8 = ltrim(rtrim(xDim.value('/x[8]', 'varchar(max)')))
            FROM (
                SELECT Cast('<x>' + replace((
                                SELECT replace(@value, '!*|*!', '§§Split§§') AS [*]
                                FOR XML Path('')
                                ), '§§Split§§', '</x><x>') + '</x>' AS XML) AS xDim
                ) AS A

            SELECT @my1 ...

It works, but the problem with it is that it only allows for a finite number of delimited strings. I need unlimited so I am trying to change it to insert into a temp table.

            Declare @value varchar(max) = '70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836092!|!???!*|*!70!|!05/28/2017!|!05/25/2017!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836093!|!???!*|*!70!|!none!|!none!|!ALLERGAN USA, INC.[A475]!|!Y!|!N!|!GERIMEDB0A!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836094!|!???!*|*!' 
            DROP TABLE #TempTable
            CREATE TABLE #TempTable ([row] varchar(max))  

            DECLARE @my1 VARCHAR(500)

            SELECT @my1 = ltrim(rtrim(xDim.value('/x[1]', 'varchar(max)')))
            FROM (
                SELECT Cast('<x>' + replace((
                                SELECT replace(@value, '!*|*!', '§§Split§§') AS [*]
                                FOR XML Path('')
                                ), '§§Split§§', '</x><x>') + '</x>' AS XML) AS xDim
                ) AS A

            INSERT INTO #TempTable ([row])
            SELECT @my1
            select * from #TempTable

This, as you can probably see only inserts 1 row. Row do I iterate through the length of the string and insert for each delimitation?

You need to split once for rows and then once again for columns. — Zohar Peled, May 16 '17 at 16:06

John Cappelletti · Accepted Answer · 2017-05-16T16:36:48.203

The following will parse the string (twice). First on the Row Delimiter, and then by the Column Delimiter.

Once this data is in a table, you could assign to variables as needed (if that is truly what you want)

Option 1 with a Parse UDF

Declare @value varchar(max) = '70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836092!|!???!*|*!70!|!05/28/2017!|!05/25/2017!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836093!|!???!*|*!70!|!none!|!none!|!ALLERGAN USA, INC.[A475]!|!Y!|!N!|!GERIMEDB0A!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836094!|!???!*|*!' 

Select RowNr=A.RetSeq
      ,B.*
 From  [dbo].[udf-Str-Parse](@value,'!*|*!') A
 Cross Apply [dbo].[udf-Str-Parse](A.RetVal,'!|!')  B

Option 2 Without a Parse UDF

Declare @value varchar(max) = '70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836092!|!???!*|*!70!|!05/28/2017!|!05/25/2017!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836093!|!???!*|*!70!|!none!|!none!|!ALLERGAN USA, INC.[A475]!|!Y!|!N!|!GERIMEDB0A!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836094!|!???!*|*!' 

Select RowNr=A.RetSeq
      ,B.*
 From  (
        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(@value,'!*|*!','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
       ) 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.RetVal,'!|!','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             )  B

Both Return

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

EDIT - A combination of the two techniques

Declare @value varchar(max) = '70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836092!|!???!*|*!70!|!05/28/2017!|!05/25/2017!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836093!|!???!*|*!70!|!none!|!none!|!ALLERGAN USA, INC.[A475]!|!Y!|!N!|!GERIMEDB0A!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836095!|!???!*|*!70!|!none!|!none!|!J500!|!JOHNSON & JOHNSON HEALTHCARE SYSTEM!|!N!|!77836094!|!???!*|*!' 

Select RowNr=A.RetSeq
      ,B.*
 From  (
        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(@value,'!*|*!','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
       ) 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)')))
                      ,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(A.RetVal,'!|!','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             )  B
 Where A.RetVal is not null

Returns

@DavidTunnell Happy to help. But looking at you question, Do you have a max number of columns, and unlimited number of rows? because it would be a small matter to pivot — John Cappelletti, May 16 '17 at 16:34

Delimiting string/varchar into rows to insert into #temp table

1 Answers1

Linked