Retrieve numbers From a String


1. Recursive CTE:

declare @s varchar(100)='abcdef923ghicde456'

declare @pos int

;with mycte as

(select @s as numCol, PATINDEX('%[^0-9]%', UPPER(@s)) pos

union all

Select cast(REPLACE(numCol, SUBSTRING(numCol, pos, 1), '') as varchar(100)) numCol,

PATINDEX('%[^0-9]%', REPLACE(numCol, SUBSTRING(numCol, pos, 1), '')) pos from mycte WHERE mycte.pos>0

)

select numCol from mycte WHERE pos=0

--OPTION (MAXRECURSION 100);

2.Loop

declare @s varchar(100)='abcdef923ghicde456'

declare @pos int

SET @pos = PATINDEX('%[^0-9]%',@s);

WHILE @pos > 0

BEGIN;

SET @s = REPLACE(@s, SUBSTRING(@s, @pos, 1), '');

SET @pos = PATINDEX('%[^0-9]%', @s);

END;

select @s

3. Tally table:

http://beyondrelational.com/modules/2/blogs/70/posts/10821/extract-only-numbers-from-a-string.aspx

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 t1

) as t2

select @result as only_numbers

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 t1

) as t2

select @result as only_Non-numbers

Advertisements


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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s