Extract only numbers from a String

Sometimes we may need to extract only numbers from a string. Usually I see people using while loop to check each and every character to see if it is a number and extract it

Here is a different approach

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


Result

4093978
Published Tuesday, December 18, 2007 2:48 PM by Madhivanan

Comments

# Count number of words in a string

Friday, December 21, 2007 9:52 AM by Madhivanan

Tony Rogerson in his weblog posted on Counting the number of words in a string Here are two more methods

# Count number of words in a String

Friday, December 21, 2007 10:10 AM by SQL Server Transact-SQL (SSQA.net)

Tony Rogerson in his weblog posted about Counting the number of words in a string Here are two more methods

# re: Extract only numbers from a String

Tuesday, February 26, 2008 7:37 PM by Jeff M

Thanks for pointing me at this method!  Btw, the master.dbo.spt_values.type on my system is actually 'P' ( uppercase P ).

# re: Extract only numbers from a String

Wednesday, February 27, 2008 6:39 AM by Madhivanan

Hi Jeff

Yes it matters if your DB is case sensitive Smile

# Extract number from a string | Dharaa

Tuesday, August 6, 2013 6:51 AM by Extract number from a string | Dharaa

Pingback from  Extract number from a string | Dharaa