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
Filed under: , ,

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