verify if a string has only numbers

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)

verify if a string has only numbers

Author

Topic

cupeet987
Starting Member

11 Posts

Posted - 2007-12-21 : 09:48:12

Is there a quick and efficient way to verify if a varchar() datatype is composed only by algarisms 0...9 or if it also contains any characters? Or I must check by brute force?

Forgive-me for my newbiesness.
I appreciate your attention in advance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-21 : 09:53:17

declare @str varchar(50)
set @str = '1234567890'
select 'onlyNumbers' where @str not like '%[^0-9]%'

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-21 : 10:17:19

or

where isnumeric(string+'d0')=1

Madhivanan

Failing to plan is Planning to fail

cupeet987
Starting Member

11 Posts

Posted - 2007-12-21 : 12:00:15

quote:
Originally posted by madhivanan

or

where isnumeric(string+'d0')=1

Madhivanan

Failing to plan is Planning to fail

Thanks to both of you.
Howeverm I still do not understand what the +'d0' stands for.
Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 13:30:34

It is scientific notation, much as "E+2" means 100.

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-21 : 14:14:03

The original question specified: "composed only by algarisms 0...9 ". isNumeric will return 1 for decimal points as well
ie: select isnumeric('12.25'+'d0')

Be One with the Optimizer
TG

cupeet987
Starting Member

11 Posts

Posted - 2007-12-21 : 16:26:00

quote:
Originally posted by TG

The original question specified: "composed only by algarisms 0...9 ". isNumeric will return 1 for decimal points as well
ie: select isnumeric('12.25'+'d0')

Be One with the Optimizer
TG

I understand you, I'm using both solutions now.
Just curious about what that mysterious +'d0' means.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 01:36:27

quote:
Originally posted by TG

The original question specified: "composed only by algarisms 0...9 ". isNumeric will return 1 for decimal points as well
ie: select isnumeric('12.25'+'d0')

Be One with the Optimizer
TG

Yes. I thought OP may come and ask "I want to have decimal place as well" (This happended sometimes back)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 01:44:44

quote:
Originally posted by cupeet987

quote:
Originally posted by TG

The original question specified: "composed only by algarisms 0...9 ". isNumeric will return 1 for decimal points as well
ie: select isnumeric('12.25'+'d0')

Be One with the Optimizer
TG

I understand you, I'm using both solutions now.
Just curious about what that mysterious +'d0' means.

As said it is in scientific notation which adds 0 to the original string. As ISNUMERIC() is not reliable, you can make use of that technique

select col from
(
	select 'test' as col union all
	select '978.345' as col union all
	select '567' as col union all
	select '12d8' as col 
) as t
where isnumeric(col)=1

select col from
(
	select 'test' as col union all
	select '978.345' as col union all
	select '567' as col union all
	select '12d8' as col 
) as t
where isnumeric(col+'d0')=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources