declare @tbaleOne table (
   tbale_id int identity(1,1) not null,
   Column1 varchar(100) not null,
   primary key(tbale_id)
);

insert @tbaleOne( Column1)
select 'AA,BB,CC'
union all select 'DD,EE,FF'
union all select 'GG,HH,II';

set ansi_warnings off;

select max(case x.pos when 1 then x.val end) as col1,
   max(case x.pos when 4 then x.val end) as col2,
   max(case x.pos when 7 then x.val end) as col3
from @tbaleOne i
   cross apply (
         select 1 as pos, substring(i.Column1, 1, 2) as val
         union all
         select 4 as pos, substring(i.Column1, 4, 2) as val
         union all
         select 7 as pos, substring(i.Column1, 7, 2) as val
      ) x
group by i.tbale_id;

set ansi_warnings on;

declare @t table(c varchar(100))
insert @t values ('AA,BB,CC'),('DD,EE,FF'),('GG,HH,II')

select
	col1 = parsename(replace(c,',','.'),3),
	col2 = parsename(replace(c,',','.'),2),
	col3 = parsename(replace(c,',','.'),1)
from
	@t

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

================================================================================
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.

select
	@HOLDER1 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),3),
	@HOLDER2 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),2),
	@HOLDER3 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),1)
from
	#testEnvironment