Extract/parse info between delimiters

Question

I have a column where there are 10 "fields" separated by the pipe character, looks like this:

texthere | somemoretext | sometinghere | moreinfo | evenmorehere | etc | etc |

I've been trying to extract what's between the pipes and I can do the first 2, but after that, my brain gets stuck in an inception loop and I can't wrap my head around it.

eg. Table name is MyTable, column is MyColumn

SELECT TOP 10  MyTable.ItemsID, MyTable.MyColumn
(SELECT SUBSTRING (MyTable.MyColumn,1,CHARINDEX('|', MyColumn)-1))as Pos1,
(SELECT SUBSTRING(MyTable.MyColumn, CHARINDEX('|', MyColumn)+1,CHARINDEX('|', MyColumn))) as Pos2
FROM MyTable

I get what I need for Positions 1 and 2, but then for the rest, I'm not sure how to do it.

I'm thinking I need to get the index pos of my 3rd pipe but my brain freezes, I tried variations of:

SELECT SUBSTRING(MyTable.MyColumn, CHARINDEX(SUBSTRING(MyTable.MyColumn, CHARINDEX('|', MyColumn)+1,CHARINDEX('|', MyColumn)))) as Pos3

I've just looked into REPLACE and PARSENAME functions. With replace I could replace the pipe characters with dots or periods so that PARSENAME can parse the dot delimited values. However, I discovered that PARSENAME is limited to 4 values.

I got nice results using STRING_SPLIT however, this function requires compatibility level 130 and I can't alter the DB. My interactions with it is through APIs.

SELECT value
FROM STRING_SPLIT ((select mytable.mycolumn from dbo.mytable), '|'); 

Answer 1

I use this script usually:

create table #temp (id int, val varchar(50))
insert into #temp (id,val)
values
(1,'aaa|bbb|ccc|ddd'),
(2,'aaa|bbb|ccc|ddd'),
(3,'aaa|bbb|ccc|ddd'),
(4,'aaa|bbb|ccc|ddd')

select * from (
select * from #temp as t 
cross apply (select num=Row_Number()
                  over (Order by (SELECT NULL)),
                value
             from string_split(t.val, '|') ) d) s 
pivot (max(value) for s.num in([1],[2],[3],[4])) a

You just have to add 6 more fieds.

This solution relies on STRING_SPLIT that is available since sql server 2016.

Relay on this for alternatives:

link

Answer 2

You could use one of the string splitting functions people used before STRING_SPLIT was a thing.

If you don't want to create a function in your database, or just want to implement this using CHARINDEX for educational purposes, have a look at this:

DECLARE @MyTable table 
(
    ItemsID integer PRIMARY KEY, 
    MyColumn varchar(100) NOT NULL
);

INSERT @MyTable 
    (ItemsID, MyColumn)
VALUES
    (1, '1|2|3|4|5|6|7|8|9|0'),
    (2, 'AB|CD|EF|GH|IJ|KL|MN|OP|QR|ST');

SELECT
    MT.ItemsID,
    MT.MyColumn,
    Splits.Item1,
    Splits.Item2,
    Splits.Item3,
    Splits.Item4,
    Splits.Item5,
    Splits.Item6,
    Splits.Item7,
    Splits.Item8,
    Splits.Item9,
    Splits.Item10 
FROM @MyTable AS MT
CROSS APPLY 
(
    SELECT
        Item1 = LEFT(MT.MyColumn, CA1.p1 - 1),
        Item2 = SUBSTRING(MT.MyColumn, CA1.p1 + 1, CA2.p2 - CA1.p1 - 1),
        Item3 = SUBSTRING(MT.MyColumn, CA2.p2 + 1, CA3.p3 - CA2.p2 - 1),
        Item4 = SUBSTRING(MT.MyColumn, CA3.p3 + 1, CA4.p4 - CA3.p3 - 1),
        Item5 = SUBSTRING(MT.MyColumn, CA4.p4 + 1, CA5.p5 - CA4.p4 - 1),
        Item6 = SUBSTRING(MT.MyColumn, CA5.p5 + 1, CA6.p6 - CA5.p5 - 1),
        Item7 = SUBSTRING(MT.MyColumn, CA6.p6 + 1, CA7.p7 - CA6.p6 - 1),
        Item8 = SUBSTRING(MT.MyColumn, CA7.p7 + 1, CA8.p8 - CA7.p7 - 1),
        Item9 = SUBSTRING(MT.MyColumn, CA8.p8 + 1, CA9.p9 - CA8.p8 - 1),
        Item10 = RIGHT(MT.MyColumn, LEN(MT.MyColumn) - CA9.p9)
    FROM (SELECT CHARINDEX('|', MT.MyColumn)) AS CA1 (p1)
    CROSS APPLY (SELECT CHARINDEX('|', MT.MyColumn, CA1.p1 + 1)) AS CA2 (p2)
    CROSS APPLY (SELECT CHARINDEX('|', MT.MyColumn, CA2.p2 + 1)) AS CA3 (p3)
    CROSS APPLY (SELECT CHARINDEX('|', MT.MyColumn, CA3.p3 + 1)) AS CA4 (p4)
    CROSS APPLY (SELECT CHARINDEX('|', MT.MyColumn, CA4.p4 + 1)) AS CA5 (p5)
    CROSS APPLY (SELECT CHARINDEX('|', MT.MyColumn, CA5.p5 + 1)) AS CA6 (p6)
    CROSS APPLY (SELECT CHARINDEX('|', MT.MyColumn, CA6.p6 + 1)) AS CA7 (p7)
    CROSS APPLY (SELECT CHARINDEX('|', MT.MyColumn, CA7.p7 + 1)) AS CA8 (p8)
    CROSS APPLY (SELECT CHARINDEX('|', MT.MyColumn, CA8.p8 + 1)) AS CA9 (p9)
) AS Splits;

Output:

ItemsID	MyColumn	Item1	Item2	Item3	Item4	Item5	Item6	Item7	Item8	Item9	Item10
1	1|2|3|4|5|6|7|8|9|0	1	2	3	4	5	6	7	8	9	0
2	AB|CD|EF|GH|IJ|KL|MN|OP|QR|ST	AB	CD	EF	GH	IJ	KL	MN	OP	QR	ST