TSQL Parse Pipe Delimited String Into Columns

I'm working with an database whose table with a column containing multiple pipe delimited string values stored.  I found a nice function called dbo.Wordparser (shown below) which allowed me to parse the pipe delimited string into multiple columns.

The problem I'm running into is when it gets to the 4th and 5th column values it puts the 5th data element into the 4th column versus putting it in the 5th column.

Here's an example of the dataset, you'll notice original row 1 has CONTACTID value set in OLDVALUES but original row 2 has CONTACTID value as NULL and COMPANYID value set.
declare @tmp table (ID int, MODDATE datetime, COLNAME nvarchar(max), OLDVALUE nvarchar(max))

insert @tmp select 1, '2017-11-03 00:00:00.000','IQREGION|LINKID|EVENTID|CONTACTID|COMPANYID|FUNDID|INVESTID','ab11|22cde||BAMickey|||1'
insert @tmp select 2, '2017-11-03 00:00:00.000','IQREGION|LINKID|EVENTID|CONTACTID|COMPANYID|FUNDID|INVESTID','bc22|33cde|||MinnieMouse||1'

Open in new window


Example of the Dataset Output
example_dataset_output.png
Example of the Select using the Function
select *
from (
select ID, MODDATE
, dbo.Wordparser(COLNAME,1) as COL1
, dbo.Wordparser(OLDVALUE,1) as COL2
, 1 as COLORDER
from @tmp

union

select ID, MODDATE
, dbo.Wordparser(COLNAME,2) as COL1
, dbo.Wordparser(OLDVALUE,2) as COL2
, 2 as COLORDER
from @tmp

union

select ID, MODDATE
, dbo.Wordparser(COLNAME,3) as COL1
, dbo.Wordparser(OLDVALUE,3) as COL2
, 3 as COLORDER
from @tmp

union

select ID, MODDATE
, dbo.Wordparser(COLNAME,4) as COL1
, dbo.Wordparser(OLDVALUE,4) as COL2
, 4 as COLORDER
from @tmp

union

select ID, MODDATE
, dbo.Wordparser(COLNAME,5) as COL1
, dbo.Wordparser(OLDVALUE,5) as COL2
, 5 as COLORDER
from @tmp

union

select ID, MODDATE
, dbo.Wordparser(COLNAME,6) as COL1
, dbo.Wordparser(OLDVALUE,6) as COL2
, 6 as COLORDER
from @tmp
) x
order by 1,5

Open in new window


Example of the Select Output
example_select_output.png
Example of the Function
CREATE FUNCTION [dbo].[Wordparser]
(
  @multiwordstring VARCHAR(255),
  @wordnumber      NUMERIC
)
returns VARCHAR(255)
AS
  BEGIN
      DECLARE @remainingstring VARCHAR(255)
      SET @remainingstring=@multiwordstring

      DECLARE @numberofwords NUMERIC
      SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, '|', '')) + 1)

      DECLARE @word VARCHAR(50)
      DECLARE @parsedwords TABLE
      (
         line NUMERIC IDENTITY(1, 1),
         word VARCHAR(255)
      )

      WHILE @numberofwords > 1
        BEGIN
            SET @word=LEFT(@remainingstring, CHARINDEX('|', @remainingstring) - 1)

            INSERT INTO @parsedwords(word)
            SELECT @word

            SET @remainingstring= REPLACE(@remainingstring, Concat(@word, '|'), '')
            SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, '|', '')) + 1)

            IF @numberofwords = 1
              BREAK

            ELSE
              CONTINUE
        END

      IF @numberofwords = 1
        SELECT @word = @remainingstring
      INSERT INTO @parsedwords(word)
      SELECT @word

      RETURN
        (SELECT word
         FROM   @parsedwords
         WHERE  line = @wordnumber)

  END
GO

Open in new window

KANEDA 0149Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
I am a not a fan of function as they dont perform well. We need to use SET based approach.

Please check this and let us know in case of any issues.

Please see this --

declare @tmp table (ID int, MODDATE datetime, COLNAME nvarchar(max), OLDVALUE nvarchar(max))
insert @tmp select 1, '2017-11-03 00:00:00.000','IQREGION|LINKID|EVENTID|CONTACTID|COMPANYID|FUNDID|INVESTID','ab11|22cde||BAMickey|||1'
insert @tmp select 2, '2017-11-03 00:00:00.000','IQREGION|LINKID|EVENTID|CONTACTID|COMPANYID|FUNDID|INVESTID','bc22|33cde|||MinnieMouse||1'

;WITH CTE AS
(
	SELECT * , CAST('<A>'+ REPLACE(OLDVALUE,'|','</A><A>')+ '</A>' AS XML) rt 
	, CAST('<A>'+ REPLACE(COLNAME,'|','</A><A>')+ '</A>' AS XML) rt1
	FROM @tmp t
)
,CTE1 AS
(
	SELECT ID,MODDATE,u1.Value,ROW_NUMBER() OVER (PARTITION BY ID Order BY Id) rnk FROM CTE c
	CROSS APPLY
	(
		SELECT t.value('.', 'VARCHAR(10)') Value FROM rt1.nodes('/A') AS x(t)	
	)u1
)
,CTE2 AS
(
	SELECT ID,MODDATE,u.Value,ROW_NUMBER() OVER (PARTITION BY ID Order BY Id) rnk FROM CTE
	CROSS APPLY
	(
		SELECT t.value('.', 'VARCHAR(10)') Value FROM rt.nodes('/A') AS x(t)	
	)u
)
SELECT c1.ID,c1.MODDATE,c1.Value,c2.Value,ROW_NUMBER() OVER (PARTITION BY c1.ID ORDER BY c1.ID) ColOrder FROM CTE1 c1 INNER JOIN CTE2 c2
ON c1.ID = c2.ID AND c1.MODDATE = c2.MODDATE AND c2.rnk = c1.rnk
 

Open in new window


OUTPUT

/*------------------------
OUTPUT 
------------------------*/

(1 row(s) affected)

(1 row(s) affected)
ID          MODDATE                 Value      Value      ColOrder
----------- ----------------------- ---------- ---------- --------------------
1           2017-11-03 00:00:00.000 IQREGION   ab11       1
1           2017-11-03 00:00:00.000 LINKID     22cde      2
1           2017-11-03 00:00:00.000 EVENTID               3
1           2017-11-03 00:00:00.000 CONTACTID  BAMickey   4
1           2017-11-03 00:00:00.000 COMPANYID             5
1           2017-11-03 00:00:00.000 FUNDID                6
1           2017-11-03 00:00:00.000 INVESTID   1          7
2           2017-11-03 00:00:00.000 IQREGION   bc22       1
2           2017-11-03 00:00:00.000 LINKID     33cde      2
2           2017-11-03 00:00:00.000 EVENTID               3
2           2017-11-03 00:00:00.000 CONTACTID             4
2           2017-11-03 00:00:00.000 COMPANYID  MinnieMous 5
2           2017-11-03 00:00:00.000 FUNDID                6
2           2017-11-03 00:00:00.000 INVESTID   1          7

(14 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Which SQL Server version are you using.
0
 
KANEDA 0149Author Commented:
SQL Server 2008 R2
0
 
KANEDA 0149Author Commented:
Thanks Pawan, this worked perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.