Search

Friday, September 30, 2011

Extracting Only Numbers from a String

Sometimes we may need to extract only numbers from a string.
The simple approach is to run a while loop on given string to check each and every character and extract it to get the result.
Here is one alternative approach:

Declare @strTemp varchar(100),@strResult varchar(100)
set @strTemp='1zxcv123asd5fqw4er'
set @strResult=''
select @
strResult = @strResult+
case when number like '[0-9]' then number else '' end from
(select substring(@
strTemp,number,1) as number from 
(select number from master..spt_values 
where type='p' and number between 1 and len(@
strTemp)) as t
) as t 
select @
strResult as [Numbers] 
go

Result:
112354

No comments:

Post a Comment