I ran into this a while back. I came up with a solution, but it is not the best. Create a function in the database that takes one argument (value). It returns a stripped value that removes all non-numeric characters from a varchar string.

USE:

Update [Table] Set [Column] = dbo.fn_StripNONNumeric([Column]) WHERE ISNUMERIC([Column]) = 0

CREATE FUNCTION [dbo].[fn_StripNONNumeric] (@Value_In varchar(250))

Returns varchar(250)

AS

BEGIN

DECLARE @sChar CHAR(1), @Value_Out_stripped VARCHAR(250)

DECLARE@iStrLen bigint , @iStrPos bigint

--init variables

SELECT @iStrPos = 1

SELECT @Value_Out_stripped = ''

--get the string length

SELECT @iStrLen = LEN(@Value_In)

--loop through the set

WHILE @iStrPos <= @iStrLen

BEGIN

--get each character

SELECT @sChar = SUBSTRING(@Value_In,@iStrPos,1)

--make sure its between 0-9, A-Z, or a-z

IF ASCII(@sChar) >= 48 AND ASCII(@sChar) <= 57

SELECT @Value_Out_stripped = @Value_Out_stripped + @sChar

--increament counter

SELECT @iStrPos = @iStrPos + 1

END

RETURN @Value_Out_stripped

END