differentiate bewteen integrs and characters

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

SQL Server 2005 Forums

Transact-SQL (2005)

differentiate bewteen integrs and characters

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-01-05 : 16:43:24

i have varchar field name customer value where the values are in this manner

123232
item
xxx
23456
12344
item...

how can i get to select only the fields with integers....
ie. just 123232
23456
12344

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-05 : 16:59:25

See madhi's:
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-01-05 : 18:53:35

thank you so much

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2010-01-05 : 23:33:30

Hi Try this is also

declare @a varchar(40),@start int, @end int ,@number varchar(1024), @sting varchar(1024),@var varchar(1)
select @a = '12ads235'
SELECT @end = datalength(@a),@start = 1 ,@number = '',@sting = ''
while ( @start <= @end)
BEGIN
SELECT @var = substring(@a,@start,1)
IF isnumeric(@var) = 1
SET @number = @number+@var
ELSE
SET @sting = @sting+@var
SELECT @start = @start+1
END
select @number as Number ,@sting as string

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 03:26:09

quote:
Originally posted by Nageswar9

Hi Try this is also

declare @a varchar(40),@start int, @end int ,@number varchar(1024), @sting varchar(1024),@var varchar(1)
select @a = '12ads235'
SELECT @end = datalength(@a),@start = 1 ,@number = '',@sting = ''
while ( @start <= @end)
BEGIN
SELECT @var = substring(@a,@start,1)
IF isnumeric(@var) = 1
SET @number = @number+@var
ELSE
SET @sting = @sting+@var
SELECT @start = @start+1
END
select @number as Number ,@sting as string

check the link posted earlier
isnumeric is not fully reliable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources