SQL Server : Get Numeric part from a varchar/string.

Found question to fetch only numeric information from a string/varchar in some of blogs, so thought let try and share my attempt.

Implement logic:
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)
set @s='fdf4ff345rg564rt5434tr'
set @result='';
with alias1(data1, data2) as
(
select 1 as data1 , SUBSTRING(@s,1,1) as data2
union all
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
from
(select data2 from alias1) as final option (MAXRECURSION 0);
select @result as only_numbers;

only_numbers
-----------------
43455645434

please note :
Added option (MAXRECURSION 0) to handle default max recursion limit (100) exception.

Reference to blog for alternate solution :
https://madhivanan.wordpress.com/2017/03/21/extract-only-numbers-from-alphanumeric-string/

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, SQL Server Learning and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s