Since we have a known number of columns, we could technically split the data via a split function and then use PARSENAME to split the other data for us.

It makes the code clean and is pretty straight forward. Of course this only holds water, if there is a set number of columns and it is less than 4.

USE [tempdb]

GO

IF OBJECT_ID('dbo.fn_split') IS NOT NULL

DROP FUNCTION dbo.fn_split;

GO

CREATE FUNCTION dbo.fn_split(@arr AS NVARCHAR(MAX), @sep AS NCHAR(1))

RETURNS TABLE

AS

RETURN

WITH

L0 AS (SELECT 1 AS C UNION ALL SELECT 1) --2 rows

,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B) --4 rows (2x2)

,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B) --16 rows (4x4)

,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B) --256 rows (16x16)

,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B) --65536 rows (256x256)

,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B) --4,294,967,296 rows (65536x65536)

,Numbers AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)

SELECT

(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,

LTRIM(SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n)) AS element

FROM Numbers

WHERE n <= LEN(@arr) + 1

AND SUBSTRING(@sep + @arr, n, 1) = @sep

AND Numbers.n <= 1000 -- make sure to change to an appropriate value

GO

DECLARE @var VARCHAR(MAX)

SET @var ='1,a,b,20080101|2,c,d,20080102|3,e,f,20080102'

SELECT

PARSENAME(REPLACE(element,',','.'),4) AS Col1,

PARSENAME(REPLACE(element,',','.'),3) AS Col2,

PARSENAME(REPLACE(element,',','.'),2) AS Col3,

PARSENAME(REPLACE(element,',','.'),1) AS Col4

FROM dbo.fn_split(@var,'|')