Here's another way that is a bit faster than using PARSENAME.

SET NOCOUNT ON;

--== SOME SAMPLE DATA ==--

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

CREATE TABLE #testEnvironment

(c VARCHAR(100))

--1,000,000 Random rows of data

;WITH Tally (n) AS (

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)

INSERT INTO #testEnvironment

SELECT CAST(ABS(CHECKSUM(NEWID()) % 15) AS VARCHAR(50)) + ',' +

CAST(ABS(CHECKSUM(NEWID()) % 15) AS VARCHAR(50)) + ',' +

CAST(ABS(CHECKSUM(NEWID()) % 15) AS VARCHAR(50))

FROM Tally;

--Holder variable to take display time out of the equation

DECLARE @HOLDER1 VARCHAR(100), @HOLDER2 VARCHAR(100), @HOLDER3 VARCHAR(100);

PRINT REPLICATE('=',80);

PRINT 'PARSENAME';

PRINT REPLICATE('=',80);

SET STATISTICS IO, TIME ON;

select

@HOLDER1 = parsename(replace(c,',','.'),3),

@HOLDER2 = parsename(replace(c,',','.'),2),

@HOLDER3 = parsename(replace(c,',','.'),1)

from

#testEnvironment

SET STATISTICS IO, TIME OFF;

PRINT REPLICATE('=',80);

PRINT 'SUBSTRING';

PRINT REPLICATE('=',80);

SET STATISTICS IO, TIME ON;

SELECT @HOLDER1=SUBSTRING(c, 1, n1 - 1)

,@HOLDER2=SUBSTRING(c, n1 + 1, LEN(c) - (n2 + 1))

,@HOLDER3=SUBSTRING(c, n2 + 2, LEN(c))

FROM #testEnvironment

CROSS APPLY (SELECT CHARINDEX(',', c), LEN(c) - CHARINDEX(',', REVERSE(c))) a(n1, n2)

SET STATISTICS IO, TIME OFF;

DROP TABLE #testEnvironment

Timing results:

================================================================================

PARSENAME

================================================================================

Table '#testEnvironment____________________________________________________________________________________________________0000000000AD'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 2481 ms, elapsed time = 2467 ms.

================================================================================

SUBSTRING

================================================================================

Table '#testEnvironment____________________________________________________________________________________________________0000000000AD'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1653 ms, elapsed time = 1673 ms.