Extract only numbers from alphanumeric string

I see few questions related to this in forums on how to extract only numbers from an alphanumeric string.

Well there can be many methods to do this. One method is to use number table and extract them using a regular expression

Declare @s varchar(100),@result varchar(100)
set @s='as4khd0939sdf78'
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

The result is

only_numbers
-------------------
4093978
Advertisements

4 thoughts on “Extract only numbers from alphanumeric string

  1. Very nice article.. thanks for sharing same.!

    We can also try generating split data using recursive With instead of using master tables as below.

    Declare @s varchar(100),@result varchar(100)
    set @s=’as4khd0939sdf78′
    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
    )
    select data2 from alias1

    PS : we would need to provide hint option (MAXRECURSION 0) to avoid max recursion exception (default 100)

    by using above method, we can support varchar information with character more then 2048 (from master..spt_values where type=’p’ rows output.)

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