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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Which SQL Server version are you using.
0
KANEDA 0149Author Commented:
SQL Server 2008 R2
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KANEDA 0149Author Commented:
Thanks Pawan, this worked perfectly!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.