Help with ISNUMERIC - write something new

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)

Help with ISNUMERIC - write something new

Author

Topic

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-07-30 : 14:08:51

As many of you know ISNUMERIC is unreliable. I've been tasked with coming up with an alternative and they would rather that I not write a user defined function. They want me to parse through each character in the field (string) and see if it's 0-9. That way if a '.', ',', '+', '-', '/', 'e', or any other characters that aren't actual numbers are found it can fail the check. I'm having trouble thinking of how to do this in a single statement. I've messed with CHARINDEX a bit but would still need looping to parse through each character in the field which points me to creating a UDF. Any thoughts on how to do it quick and simple in one statement?

Thanks,

Van

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 14:11:38

refer this:-

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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-07-30 : 14:36:23

quote:
Originally posted by Van

As many of you know ISNUMERIC is unreliable...
...That way if a '.', ',', '+', '-', '/', 'e', or any other characters that aren't actual numbers are found it can fail the check.

Uhhmmmm...both '.' and ',' can be in valid numbers...

e4 d5 xd5 Nf6

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-07-30 : 14:38:28

That helps a little but I really need a way to do it without creating a UDF and I was hoping to be able to look at each character individually and see if it's 0-9. Any other thoughts?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-30 : 14:40:52

select isnumeric(replace(replace(@yourVariable,'d','x'),'e','x')) as IsNumber

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-07-30 : 14:42:41

quote:
Originally posted by blindman

quote:
Originally posted by Van

As many of you know ISNUMERIC is unreliable...
...That way if a '.', ',', '+', '-', '/', 'e', or any other characters that aren't actual numbers are found it can fail the check.
Uhhmmmm...both '.' and ',' can be in valid numbers...

e4 d5 xd5 Nf6

Yea, I know but in my case I'm getting data from a source system (mainframe) for fields like a code that should only be numbers but the source system allows anything to be put in. So in my case 123,456 would need to fail but 123456 would be ok. Same with 123.456...it should fail. So any character in the field that has anything other than 0-9 should fail.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-30 : 14:45:44

select 1 where @variable not like '%[^0-9]%'

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-07-30 : 14:46:45

quote:
Originally posted by spirit1

select isnumeric(replace(replace(@yourVariable,'d','x'),'e','x')) as IsNumber

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Yea, I know about the replace (we're doing that actullay now for ',', '.', '+', ect) but it's scattered throughout a bunch of SPs and in some cases the previouls people who coded might not have known about 'e' causing it to be nummeric or '+' and so on. So it's different in many SPs. I've been asked to come up with a way to simply look at each character individually and see if it's 0-9 and fail if it's not.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-30 : 14:47:54

will my previous reply do?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-07-30 : 14:52:22

quote:
Originally posted by spirit1

select 1 where @variable not like '%[^0-9]%'

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Wow, that simple. I did play around with visakh16's link, but not enough apparantly. Can you tell me what the ^ does exactly and why it's needed? So far I can't find anywhere that this doesn't work for me. Thanks again.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-30 : 14:57:41

from BOL:
^ - Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

so '%[^0-9]%' selects all data that have at least one char that is not a number.
the "not like" part simple inverts this and selects all data that have only numbers

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-07-30 : 15:06:42

quote:
Originally posted by spirit1

from BOL:
^ - Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

so '%[^0-9]%' selects all data that have at least one char that is not a number.
the "not like" part simple inverts this and selects all data that have only numbers

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Makes perfect sense now. Thanks again.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-30 : 15:42:01

You may need multiple levels of checks to cover all cases.

select
	num,
	GoodNumber =
		case
		when num like '%[^0-9]%' then 0
		when isnull(isnumeric(num),0) <> 1 then 0
		else 1 end
from
	(
	select num = ''			union all
	select num = '1 3'		union all
	select num = null		union all
	select num = '123'		union all
	select num = '12a99'
	) a

Results:
num   GoodNumber  
----- ----------- 
      0
1 3   0
NULL  0
123   1
12a99 0

(5 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 05:45:36

quote:
Originally posted by spirit1

select isnumeric(replace(replace(@yourVariable,'d','x'),'e','x')) as IsNumber

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

This can be simplified to

select isnumeric(@yourVariable+'d0') as IsNumber

Madhivanan

Failing to plan is Planning to fail

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-31 : 08:45:20

quote:
Originally posted by madhivanan
This can be simplified to

select isnumeric(@yourVariable+'d0') as IsNumber

Madhivanan

Failing to plan is Planning to fail

interesting and what does [+ 'd0'] do?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 09:25:41

quote:
Originally posted by rohitkumar

quote:
Originally posted by madhivanan
This can be simplified to

select isnumeric(@yourVariable+'d0') as IsNumber

Madhivanan

Failing to plan is Planning to fail

interesting and what does [+ 'd0'] do?

It converts the numbers in scientific format where numbers that are already in scientific format become invalid numbers

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources