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

DECLARE @T TABLE
(DETAILS_ID VARCHAR(100), DETAILS VARCHAR(100))

INSERT INTO @T
SELECT 'EA22749B180C41D09B4CC986D21C8F02','2006-11-28|1900-01-01|1||PL625245|'

;WITH MySplit AS (
    SELECT DETAILS_ID, DETAILS, ItemNumber
        ,Item=CASE WHEN ItemNumber <= 2 THEN CONVERT(VARCHAR(10), CAST(Item AS DATE), 101) ELSE Item END
    FROM @T
    CROSS APPLY DelimitedSplit8K(DETAILS, '|'))
SELECT DETAILS_ID
    ,Col1=MAX(CASE WHEN ItemNumber = 1 THEN Item END) 
    ,Col2=MAX(CASE WHEN ItemNumber = 2 THEN Item END) 
    ,Col3=MAX(CASE WHEN ItemNumber = 3 THEN Item END) 
    ,Col4=MAX(CASE WHEN ItemNumber = 5 THEN Item END) 
FROM MySplit a
GROUP BY DETAILS_ID

--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== BASELINE ==========

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 180 ms.
================================================================================
--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== Scalar Function ==========

 SQL Server Execution Times:
   CPU time = 7971 ms,  elapsed time = 8242 ms.
================================================================================
--------------------------------------------------------------------------------
CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE
--------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== DelimitedSplit8K Featuring Pivot ==========
Warning: Null value is eliminated by an aggregate or other SET operation.

 SQL Server Execution Times:
   CPU time = 2200 ms,  elapsed time = 2472 ms.

--===== Suppress the auto-display of rowcounts to keep them from being     
--===== mistaken as part of the result set.    
SET NOCOUNT ON
SET STATISTICS TIME OFF;
--=====Drop temp table if it exists  for script repeatability
IF (SELECT OBJECT_ID('Tempdb.dbo.#SampleData')) IS NOT NULL
  DROP TABLE #SampleData
--=====Generate a million rows of test data matching our string pattern.
SELECT TOP 1000000
ROW_NUMBER() over (order by T1.object_id) As DETAILS_ID,
  REPLACE(CONVERT(varchar(40),newid()) ,'-','')
  + '|'
  + CONVERT(varchar(10),T1.create_date,120)
  + '|'
  + CONVERT(varchar(10),T1.modify_date,120)
  + '|'
  + CONVERT(varchar,T1.object_id)
  + '|'
  + 'PL' 
  + CONVERT(VARCHAR,ABS(CHECKSUM(NEWID()))%50000+1)
  AS DETAILS
INTO #SampleData
FROM sys.objects T1
  CROSS JOIN sys.objects T2

DECLARE @HOLDER VARCHAR(20);
  
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

PRINT '========== BASELINE ==========';
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*) 
FROM #SampleData;
SET STATISTICS TIME OFF;
PRINT REPLICATE('=',80);

PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

PRINT '========== Scalar Function ==========';
SET STATISTICS TIME ON;
SELECT
dbo.fn_parsename(DETAILS,'|',1) AS [DETAILS_ID],
dbo.fn_parsename(DETAILS,'|',2) AS [Column 1],
dbo.fn_parsename(DETAILS,'|',3) AS [Column 2],
dbo.fn_parsename(DETAILS,'|',4) AS [Column 3],
dbo.fn_parsename(DETAILS,'|',5) AS [Column 4]
FROM #SampleData;
SET STATISTICS TIME OFF;

PRINT REPLICATE('=',80);
PRINT REPLICATE('-',80);
PRINT 'CLEAR CACHE SO NEITHER QUERY GETS AN ADVANTAGE';
PRINT REPLICATE('-',80);
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;


PRINT '========== DelimitedSplit8K Featuring Pivot ==========';
SET STATISTICS TIME ON;
;WITH MySplit AS (
    SELECT DETAILS_ID, DETAILS, ItemNumber
        ,Item = CASE WHEN ItemNumber <= 3 AND ItemNumber > 1 THEN CONVERT(VARCHAR(10), CAST(Item AS DATE), 101) ELSE Item END
    FROM #SampleData
    CROSS APPLY DelimitedSplit8K(DETAILS, '|'))
SELECT DETAILS_ID
    ,Col1=MAX(CASE WHEN ItemNumber = 1 THEN Item END) 
    ,Col2=MAX(CASE WHEN ItemNumber = 2 THEN Item END) 
    ,Col3=MAX(CASE WHEN ItemNumber = 3 THEN Item END) 
    ,Col4=MAX(CASE WHEN ItemNumber = 5 THEN Item END) 
FROM MySplit a
GROUP BY DETAILS_ID

SET STATISTICS TIME OFF;

========== Scalar Function ==========
DETAILS_ID                         Column 1     Column 2     Column 3   Column 4
---------------------------------- ------------ ------------ ---------- ----------
D221ACC59D214D9AA7480BDD0186B4F5   2012-04-06   2012-04-06   3          PL32145

========== DelimitedSplit8K Featuring Pivot ==========
DETAILS_ID  Col1                               Col2         Col3            Col4
----------- ---------------------------------- ------------ --------------- ----------
1           D221ACC59D214D9AA7480BDD0186B4F5   04/06/2012   04/06/2012      PL32145

========== Scalar Function ==========
DETAILS_ID                         Column 1     Column 2     Column 3   Column 4
---------------------------------- ------------ ------------ ---------- ----------
D221ACC59D214D9AA7480BDD0186B4F5   2012-04-06   2012-04-06   3          PL32145

========== DelimitedSplit8K Featuring Pivot ==========
DETAILS_ID  Col1                               Col2         Col3            Col4
----------- ---------------------------------- ------------ --------------- ----------
1           D221ACC59D214D9AA7480BDD0186B4F5   04/06/2012   04/06/2012      PL32145

DETAILS_ID	Col1	Col2	Col3	Col4
EA22749B180C41D09B4CC986D21C8F02	11/28/2006	01/01/1900	1	PL625245