try this function....
CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
SET @Count = @Count + 1
END
RETURN @IntNumbers
END
Run following script in query analyzer.
SELECT ExtractInteger('abc9-82nm@$%L')
It will return following values.
982
Instead of a function Single Query...
Declare @s varchar(100),@result varchar(100)
set @s='abc9-82nm@$%'
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
select @result as only_numbers
Output:
only_numbers
982