Found question to fetch only numeric information from a string/varchar in some of blogs, so thought let try and share my attempt.
1. Using Recursive With clause, segregated each and every character as separate row.
2. Using Regex like check for numeric information [0-9]
3. Concat all number based on regex match and project same.
Declare @s varchar(100),@result varchar(100)
with alias1(data1, data2) as
select 1 as data1 , SUBSTRING(@s,1,1) as data2
select data1+1 , SUBSTRING(@s,data1+1,1)
from alias1 where LEN(@s) > data1 --**Important, does not remove condition
select @result = @result + case when data2 like '[0-9]' then data2 else '' end
(select data2 from alias1) as final option (MAXRECURSION 0);
select @result as only_numbers;
please note :
Added option (MAXRECURSION 0) to handle default max recursion limit (100) exception.
Reference to blog for alternate solution :