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 18 December 2007 14:48 by Madhivanan

Comments

# Count number of words in a string

21 December 2007 09:52 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

21 December 2007 10:10 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

26 February 2008 19:37 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

27 February 2008 06:39 by Madhivanan

Hi Jeff

Yes it matters if your DB is case sensitive Smile

# Extract number from a string | Dharaa

06 August 2013 06:51 by Extract number from a string | Dharaa

Pingback from  Extract number from a string | Dharaa