Extracting Only Numbers from a String

While working on projects, sometimes we may need to exatract only numbers from a string.

The regular approach is to run a while loop on given string to check each and every character and extract it to get the result.

Here is alternative approach I read in another article,

Declare @str varchar(100),@result_num varchar(100)
set @str='zr3jgc124785ce64'
set @result_num=''
select @result_num = @result_num+
case when number like '[0-9]' then number else '' end from
(select substring(@str,number,1) as number from
(select number from master..spt_values
where type='p' and number between 1 and len(@str)) as t
) as t
select @result_num as [only numbers]
go

Result:
312478564

Reference : DP (http://dptechnicalblog.blogspot.com/ )