Pull Integer From String in SQL

Sometimes you find it necessary to pull Integers from a String. Instead of parsing the value, you can use this SQL function to pull an Integer value from a String. You just need to create this function using your Enterprise Manager, and call the function like this:
select dbo.GetIntegerFromString_GD("abc123") .

You will get returned 123.



CREATE FUNCTION dbo.GetIntegerFromString_GD
(@S varchar(100))
RETURNS INT
AS
BEGIN

Declare @result varchar(100)
set @result=''


select
@result=@result+case when number like '[0-9]' then number else '' end from
(
select substring(@s,number,1) as number from
(
select number from master..spt_values where type='p' and number between 1 and len(@s)
) as t
) as t


RETURN ( select @result as only_numbers )
END