Extract only numbers from alphanumeric string

Please note that this post is now available at https://exploresql.com/2017/03/21/extract-only-numbers-from-alphanumeric-string/

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s