Split pipe lined string into Rows - Split the empty values also in SQL Server

Question

I have a Pipe lined String like below:

'1|Content|2017-02-11|Guest|Gold|||||1903'

I want to split the String value into Rows. I found many suggestions by surfing on the net. Most people suggest the function dbo.Split.

When I try to split my string using that function:

SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL))RN, Items 
FROM dbo.Split('1|Content|2017-02-11|Guest|Gold|||||1903','|') 

It gives the result like below:

RN  Items
1   1
2   Content
3   2017-02-11
4   Guest
5   Gold
6   1903

It skips all the empty values and give only the value holding rows. but in my case if any values where empty, then I want it like below:

RN  Items
1   1
2   Content
3   2017-02-11
4   Guest
5   Gold
6   
7
8
9
10  1903

Which means, I want the empty vales as ' '. I tried and I can't get it. Please help me to get this. Thanks.

Answer 1

The simple way is here use XML Method to convert your Pipe lined string data with row :

DECLARE @DATA NVARCHAR(MAX);

SET @DATA = '1|Content|2017-02-11|Guest|Gold|||||1903';

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RN,
       split.a.value('.', 'VARCHAR(MAX)') [Items]
FROM
(
    SELECT CAST('<X>'+REPLACE(@DATA, '|', '</X><X>')+'</X>' AS XML) AS String
) A
CROSS APPLY String.nodes('/X') AS split(a);

Result :

RN  Items
1   1
2   Content
3   2017-02-11
4   Guest
5   Gold
6   
7
8
9
10  1903