Sample Header Ad - 728x90

Extract/parse info between delimiters

0 votes
2 answers
2320 views
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), '|');
Asked by bravo4one (101 rep)
Apr 4, 2022, 10:37 PM
Last activity: Apr 18, 2024, 12:04 AM