[SQL] How to retrieve data between pipes (delimiter) in string

I have a single table called #ParsedBlocks that contains one column called [BlockData].
This column contains a string that's pipe delimited.
I need to return the data between the pipe characters into separate columns.

So I've figured out how to extract the first column with the below script but I can't figure out how to extract from the second column onwards

select *
,SUBSTRING(BlockData, 1, CHARINDEX('|',BlockData) -1)
,SUBSTRING(BlockData, CHARINDEX('|', BlockData) + 1, LEN(BlockData))
from
#ParsedBlocks


The result of the above script:

Column1 Column2
SiteCode1 ItemCode1
SiteCode1 ItemCode2

Source Dataset:

BlockData
SiteCode1
SiteCode1

Required Result:

Column1 Column2 Column3 Column4
Sitecode1 ItemCode1 NULL CostPrice1
Sitecode1 ItemCode2 NULL CostPrice2

I have a couple of these ParsedBlocks tables that I need to apply this logic to and each table has a different number of pipe delimiters (columns) so it would be great if there was a way to have a single script to easily apply to each table.
For example:
Having a script that "automatically" finds each pipe (column) and returns the data accordingly, if that makes sense...

Continue reading...