Stripping out all non-numerical characters

DECLARE @String VARCHAR(8000) = 'ABC12D34E56';

WITH Seeds(Seed)
AS (SELECT *
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (C)),
Numbers(Number)
AS (SELECT TOP (8000)
ROW_NUMBER() OVER (ORDER BY S1.Seed)
FROM Seeds AS S1
CROSS JOIN Seeds AS S2
CROSS JOIN Seeds AS S3
CROSS JOIN Seeds AS S4)
SELECT (
SELECT SUBSTRING(@String, Number, 1)
FROM Numbers
WHERE Number <= LEN(@String)
AND SUBSTRING(@String, Number, 1) LIKE '[0-9]'
ORDER BY Number
FOR XML PATH(''),
TYPE).value('.[1]', 'VARCHAR(8000)');

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 Seeds(Seed)
AS (SELECT *
FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (C)),
Numbers(Number)
AS (SELECT TOP (8000)
ROW_NUMBER() OVER (ORDER BY S1.Seed)
FROM Seeds AS S1
CROSS JOIN Seeds AS S2
CROSS JOIN Seeds AS S3
CROSS JOIN Seeds AS S4)
SELECT *
FROM #T AS T
CROSS APPLY (SELECT (
SELECT SUBSTRING(Col1, Number, 1)
FROM Numbers
WHERE Number <= LEN(Col1)
AND SUBSTRING(Col1, Number, 1) LIKE '[0-9]'
ORDER BY Number
FOR XML PATH(''),
TYPE).value('.[1]', 'VARCHAR(8000)') AS Stripped) AS Parser;

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;

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)

GSquared (12/18/2012)

Here's one way to do it in T-SQL:

DECLARE @String VARCHAR(8000) = 'ABC12D34E56';

WITH    Seeds(Seed)
          AS (SELECT    *
              FROM      ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (C)),
        Numbers(Number)
          AS (SELECT TOP (8000)
                        ROW_NUMBER() OVER (ORDER BY S1.Seed)
              FROM      Seeds AS S1
                        CROSS JOIN Seeds AS S2
                        CROSS JOIN Seeds AS S3
                        CROSS JOIN Seeds AS S4)
    SELECT (
            SELECT  SUBSTRING(@String, Number, 1)
            FROM    Numbers
            WHERE   Number <= LEN(@String)
                    AND SUBSTRING(@String, Number, 1) LIKE '[0-9]'
            ORDER BY Number
    FOR     XML PATH(''),
                TYPE).value('.[1]', 'VARCHAR(8000)');

Alternatively, if you need to apply this to a table instead of a variable:

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    Seeds(Seed)
          AS (SELECT    *
              FROM      ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1) ) AS V (C)),
        Numbers(Number)
          AS (SELECT TOP (8000)
                        ROW_NUMBER() OVER (ORDER BY S1.Seed)
              FROM      Seeds AS S1
                        CROSS JOIN Seeds AS S2
                        CROSS JOIN Seeds AS S3
                        CROSS JOIN Seeds AS S4)
    SELECT  *
    FROM    #T AS T
            CROSS APPLY (SELECT (
                                SELECT  SUBSTRING(Col1, Number, 1)
                                FROM    Numbers
                                WHERE   Number <= LEN(Col1)
                                        AND SUBSTRING(Col1, Number, 1) LIKE '[0-9]'
                                ORDER BY Number
                         FOR    XML PATH(''),
                                    TYPE).value('.[1]', 'VARCHAR(8000)') AS Stripped) AS Parser;

In either case, it uses a "Runtime Numbers Table" to parse the string into individual characters, then strips out anything other than digits 0-9, then uses For XML to put it all back together. (FOR XML PATH with a zero-length root indicated by (''), and no column name for the query, will concatenate strings together nicely. It's a documented trick that comes in very handy.)

Does that help?