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), '|');