Numeric split - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Numeric split

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-12 : 05:11:12

Hi

I have data like this...


create table #test

(

 Accounts VARCHAR(500)

)

 --Test Values

insert into #test values('SDS3232')

insert into #test values('SS3FG262')

insert into #test values('SDS3TR28G2')

insert into #test values('SDSE3R2G32')

insert into #test values('SDS3212RR')

insert into #test values('SDS3235FGT')

insert into #test values('1234')

I need remove all the characters and returns only numeric.

I have done like this...
CREATE FUNCTION dbo.cahr(@Input varchar(1000))

RETURNS VARCHAR(1000)

BEGIN

DECLARE @Pos INT

SET @Pos = PATINDEX('%[^0-9]%',@Input)

WHILE @Pos > 0

BEGIN

SET @Input = STUFF(@Input,@Pos,1,'')

SET @Pos = PATINDEX('%[^0-9]%',@Input)

END

RETURN @Input

END

GO

Guys any other smart method...pls....

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-12 : 05:16:29

fnFilterString

KH
[spoiler]Time is always against us[/spoiler]

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-12 : 05:31:05

Hi KH

I need only string

The link i think its only for Digits...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-12 : 05:38:37

[code]
SELECT dbo.fn_FilterString('SDS3TR28G2', '[^0-9]', 0)

or

select dbo.fnFilterString('SDS3TR28G2', '[^0-9]')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-12 : 05:47:47

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-12 : 06:08:05

Hi madhi

your blog is restricted here please post ur query here if possible.

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-12 : 07:16:18

quote:
Originally posted by WoodHouse

Hi madhi

your blog is restricted here please post ur query here if possible.

thanks

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

Madhivanan

Failing to plan is Planning to fail

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-12 : 07:22:34

Hi

Thanks a lot madhi....

I need only chars so i have done bit alter...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-12 : 07:36:00

Change [0-9] to [a-z]

Madhivanan

Failing to plan is Planning to fail

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-12 : 07:41:14