This can also be done using the PatternSplitCM function described in the fourth article in my signature line (Splitting Strings Based on Patterns).

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

DROP TABLE #T;

CREATE TABLE #T

(ID INT IDENTITY

PRIMARY KEY,

Col1 VARCHAR(8000));

INSERT INTO #T

(Col1)

VALUES ('123A'),

('B1C2D3');

;WITH CTE AS (

SELECT *

FROM #T a

CROSS APPLY PatternSplitCM(a.Col1, '[0-9]')

WHERE [Matched] = 1)

SELECT ID, Col1=MAX(Col1), Col2=(

SELECT '' + Item

FROM CTE b

WHERE a.ID = b.ID

ORDER BY ItemNumber

FOR XML PATH(''))

FROM CTE a

GROUP BY ID

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

DROP TABLE #T;

In fact the inspiration for that article (described therein) was a forum-posted question that was quite similar to this one.

One caveat though. Since you're working in SQL 2005, you'll need to replace the numbers table with a Ben-Gan style Tally table something like this one (from the PatternSplitQU FUNCTION also in that article):

WITH Nbrs_3(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)

,Nbrs_2 (n) AS (SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2)

,Nbrs_1 (n) AS (SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2)

,Nbrs_0 (n) AS (SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2)

,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) As n FROM Nbrs_0)