Convert Character Data to Numeric

-- =============================================
-- Procedure to remove non-numeric characters
-- from field in data prep for conversion to
-- numeric data type.
-- =============================================

-- =============================================
-- example to execute the store procedure
-- (example loads the @p2_output variable with
-- result 112345)
-- =============================================
-- DECLARE @p2_output NUMERIC(20)
-- EXEC Char2Numeric '1-1ABC-2345', @p2_output OUTPUT
-- PRINT @p2_output

IF EXISTS (SELECT name FROM sysobjects WHERE  name = N'Char2Numeric' AND type = 'P') DROP PROCEDURE Char2Numeric
GO

CREATE PROCEDURE Char2Numeric 
@p1 VARCHAR(20) = '', @p2 NUMERIC(20) OUTPUT
AS

DECLARE @i int
DECLARE @c char(1)
DECLARE @result varchar(20)

SET @result=''
SET @i=1
WHILE @i<=LEN(@p1)
BEGIN
SET @c=SUBSTRING(@p1,@i,1)
IF @c LIKE '[0-9]'
SET @result = @result + @c
SET @i=@i+1
END

SET @p2=CAST(@result AS numeric(20))
GO