checking if a varchar value contains numeric valu

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 2000 Forums

Transact-SQL (2000)

checking if a varchar value contains numeric valu

Author

Topic

ciaran
Starting Member

40 Posts

Posted - 2005-08-03 : 12:23:59

Hi,
I have SP which pulls in a list of Promotion IDs which are stored as varchar(20). These ids should be numeric but according to murphys law they could turn out to be any character. I need to cast the promotions ids to integer values. If the cast doesn't work then proceed to the next value. I can do the iteration easily enough. I wanted to know is there an equivalent isNumeric(string X) in sql that i can use to check before i cast. Alternativaly do i have to go through each individual character and check its within a specific range before casting

dsdeming

479 Posts

Posted - 2005-08-03 : 12:26:56

There is an IsNumeric function in TSQL.

declare @a varchar( 10 )
set @a = '-152'
select isnumeric( @a )

returns 1

Dennis

ciaran
Starting Member

40 Posts

Posted - 2005-08-03 : 12:36:57

Yip you are correct.... Never knew this existed. Thanks

quote:
Originally posted by dsdeming

There is an IsNumeric function in TSQL.

declare @a varchar( 10 )
set @a = '-152'
select isnumeric( @a )

returns 1

Dennis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 02:05:17

declare @a varchar( 10 )
set @a = '-15e2'
select isnumeric( @a )

also returns 1

So the effient way is to use Like

declare @t table(i varchar(10))
insert into @t values('324')
insert into @t values('-324')
insert into @t values('32e4')
insert into @t values('3e24')
select i from @t where isnumeric(i)=1 -- returns all rows
select i from @t where i not Like '%[a-zA-Z]%' --returns first two rows

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources