ok here's one solution:

ALTER FUNCTION dbo.fn_parsename

(

@pString VARCHAR(7999),

@pDelimiter CHAR(1),

@Occurrance int

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Results VARCHAR(8000)

--===== "Inline" CTE Driven "Tally Table” produces values up to

-- 10,000... enough to cover VARCHAR(8000)

;WITH

E1(N) AS ( --=== Create Ten 1's

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) ,

--===== Do the split

InterResults

AS

(

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,

SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item

FROM cteTally

WHERE N < LEN(@pString) + 2

AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter

)

SELECT @Results = Item FROM InterResults WHERE ItemNumber = @Occurrance

return @Results

END --FUNCTION

GO

CREATE TABLE #SampleData(bigstring varchar(500) )

INSERT INTO #SampleData

SELECT 'EA22749B180C41D09B4CC986D21C8F02 2006-11-28|1900-01-01|1||PL625245|'

SELECT

dbo.fn_parsename(bigstring,'|',1) AS [DETAILS_ID],

dbo.fn_parsename(bigstring,'|',2) AS [Column 1],

dbo.fn_parsename(bigstring,'|',3) AS [Column 2],

dbo.fn_parsename(bigstring,'|',4) AS [Column 3],

dbo.fn_parsename(bigstring,'|',5) AS [Column 4]

FROM #SampleData