0

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), '|'); 
1
  • Whats the desired end goal? are you then inserting the data back into a db? Are you saving it to file? Using it in a report? Apr 18 at 3:04

2 Answers 2

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

1
  • 1
    Neither STRING_SPLIT nor Order by (SELECT NULL) guarantee order. That seems to be important here but there is no way to know based on information provided by OP
    – SMor
    Apr 5, 2022 at 11:21
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.