Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Question

Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Viewing 15 posts - 91 through 105 (of 168 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Mike McIver (12/7/2010)
Really . . . I don't know where to start. I suppose the thing to say is having command of data types is an essential for any coder. ISNUMERIC functions perfectly. Every example given is, in fact, a valid numeric.
"E" and "D" both denote scientific notation . . . would one actually expect the alternative to the example "12e34", which is 12 followed by 34 zeros, to be practical?! Search BOL for "scientific notation" to acquaint one's self with REAL, FLOAT, Constants, CAST/CONVERT, EXP . . . same goes for MONEY with respect to its valid formatting.
This article is an example of how poor coding propagates. But, let's assume the answer is (partially) correct and ask the question to the answer. "How to determine if a value is an integer?"
SELECTPATINDEX('%[^0-9]%','<whatever value>')
Only zero indicates the value is an integer.

Every point you mention is already covered in the article, with the same conclusions you came to. So I'm curious how this article is an example of how poor coding propagates. Do you mean the article suggests poor code (it suggests like where you suggest patindex, but it's essentially the same solution)? Or do you mean that the documentation in BOL of IsNumeric is a source of poor code?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Brandie Tarvin (12/7/2010)
Gail,
Am I supposed to be getting an error on the Money conversion part of your code?

Yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mike McIver SSC-Addicted Points: 493 More actions · Answer 3

The issue is ISNUMERIC returns true for values which are valid numeric data types and are not "only numeric digits" (look at the article intro again). I suppose "-1" could be something other than minus one. Regardless, in the context of the article your point is moot. If it is not, then the article's solution fails also.

But that is the problem with the article. The fact is ISNUMERIC works properly . . . interpreting scientific notation and the money data type must take precedence. The fact that a value as a string may not be intended as numeric is irrelevant. If it is also numeric then so be it. Claiming ISNUMERIC doesn't work is ridiculous.

Mike
MacroTrenz.Com
LinkedIn

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Mike McIver (12/7/2010)
The issue is ISNUMERIC returns true for values which are valid numeric data types and are not "only numeric digits" (look at the article intro again). I suppose "-1" could be something other than minus one. Regardless, in the context of the article your point is moot. If it is not, then the article's solution fails also.
But that is the problem with the article. The fact is ISNUMERIC works properly . . . interpreting scientific notation and the money data type must take precedence. The fact that a value as a string may not be intended as numeric is irrelevant. If it is also numeric then so be it. Claiming ISNUMERIC doesn't work is ridiculous.

Heh... again, look at the article, again. I actually went to bat for ISNUMERIC and the way it was described in BOL as being absolutely correct. The whole intent of the article was to teach people (based on THEIR usual question as to why ISNUMERIC supposedly doesn't work) that you simply shouldn't use ISNUMERIC as an IsAllDigits function. 🙂

I do agree as I did in article... claiming that ISNUMERIC doesn't work IS ridiculous. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Hugo Kornelis (12/7/2010)
Mike McIver (12/7/2010)
This article is an example of how poor coding propagates.
I have to disagree.
Yes, you are right - all the examples are indeed valid string representations of a value in "some" numeric data type. But there's no way to find which data type.
The only situations where I have yet seen people use functions such as ISNUMERIC, is for parsing input from a potentially erroneous source - like user input, or when parsing data sent from some third party outside the company's control. The data is expected to be in some specific numeric format, and to prevent runt-time erros, ISNUMERIC is used in an attempt to catch invalid data.
Except it fails. "12e34" being a valid floating point number is interesting, but totally irrelevant when my task is to prevent run-time errors when storing user input in a decimal(24,7) column.
The code presented in this article does not fill that gap - but it does provide a short and simple way to at least test data that has to be converted to an integer. It's far from ideal, but until Microsoft implements an IS_VALID_CONVERT function, or a set of ISINT, ISFLOAT, ISDECIMAL, ISMONEY, etc functions, we'll have to make do.

Thanks for the feedback, Hugo, but (just to clarify) the code is "ideal" for what it's advertised to do in the article... work as an "IsAllDigits" function. 🙂 The other point was to tell people what ISNUMERIC actually does so they can stop saying it's broken. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Mike McIver (12/7/2010)
Really . . . I don't know where to start. I suppose the thing to say is having command of data types is an essential for any coder. ISNUMERIC functions perfectly. Every example given is, in fact, a valid numeric.
"E" and "D" both denote scientific notation . . . would one actually expect the alternative to the example "12e34", which is 12 followed by 34 zeros, to be practical?! Search BOL for "scientific notation" to acquaint one's self with REAL, FLOAT, Constants, CAST/CONVERT, EXP . . . same goes for MONEY with respect to its valid formatting.
This article is an example of how poor coding propagates. But, let's assume the answer is (partially) correct and ask the question to the answer. "How to determine if a value is an integer?"
SELECTPATINDEX('%[^0-9]%','<whatever value>')
Only zero indicates the value is an integer.

BWAA-HAA!!!! You really need to back and do a complete read on the article. The title of the article is based on a frequently asked question and I used that title so that it'll show up near the top of a Google search. When you read the article, understand that I come right out and say that the explaination in BOL is spot on and that ISNUMERIC works EXACTLY as advertised and that is was never meant to be an "IsAllDigits" funtion. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike McIver SSC-Addicted Points: 493 More actions · Answer 7

The article's title/subtitles and other wording imply ISNUMERIC does not work properly. Here's a better way to explain the issue:

http://www.simple-talk.com/community/blogs/johnm/archive/2009/06/11/73729.aspx

Mike
MacroTrenz.Com
LinkedIn

Paulo Valued Member Points: 71 More actions · Answer 8

Craig Farrell (12/6/2010)
Paul Herbert (12/6/2010)
Thanks Jeff. Good stuff ... as usual.
I guess this makes me a lurker no more.
Hey Paul, welcome to the club.
Your brother Frank does nice work. Jeff mentions dragons, but wasn't that mostly about worms? 😉

Thanks. Glad to be here.

Dragons, worms, maggots ... it is all a matter of perspective.

(For the record, I am not related to the deceased novelist in question but I have enjoyed his works occasionally. In fact I'm not related to ANY novelist that I know of.:cool:)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Those who can make you believe absurdities,
can make you commit atrocities." ~Voltaire
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

Mike McIver (12/7/2010)
The article's title/subtitles and other wording imply ISNUMERIC does not work properly. Here's a better way to explain the issue:
http://www.simple-talk.com/community/blogs/johnm/archive/2009/06/11/73729.aspx

Heh... nope. That's just the way you took it. 😉 You missed the part where I explicitly stated "It's not a flaw... that's the way it's supposed to work!" Anyway, thanks for the feedback and the nice link. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

michael-blackburn SSC Journeyman Points: 75 More actions · Answer 10

Mike McIver (12/7/2010)
The article's title/subtitles and other wording imply ISNUMERIC does not work properly. Here's a better way to explain the issue:
http://www.simple-talk.com/community/blogs/johnm/archive/2009/06/11/73729.aspx

Yeah, see the funny thing is, many people READ articles before slamming on them. Try it sometime, you might like it!

Edit: Good link though, thanks!

TomThomson SSC Guru Points: 104773 More actions · Answer 11

steven.malone (12/7/2010)
It shows how America-centric SQL programmers are.
1d4 means One Pound, Four Pence in England.

I've spent most of my life in Britain and have never seen d used like that; I've seen 1/4d for one shilling and four pence, 1/0/4d for one pound and four pence, 1,04p for one pound and 4 new pence, but never anything like 1d4 for currency.

Where I have seen notation like 1d4 is in connection with computing, where I first saw it more than 40 years ago (and it had already been in common use for a few years). That's the only context I've seen it in. I don't believe it was ever used for currency in England (at least not in the last 60 years).

In my view though it is a disgrace that numeric expressions like '1e4' and '1d4' can't be converted direct to integer - maybe someone could explain why 10000 is not an integer? And converting '1e25' to integer should fail with a range (ie overflow) error not with an error indicating the string is incorrectly formatted.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Brandie Tarvin (12/2/2010)
Craig Farrell (12/2/2010)
How often do you re-write old code, except for a few specific core procs on any system?
Often enough that I save all my procs so I can re-use what's already written instead of rewriting it again.
Of course, in my environment, we answer a lot of Ad Hoc "Why did this happen" questions, which are always based off the same tables.

I think there's a wide spectrum of degree of rewriting, with Craig at one end, Brandie somewhere in the middle, and me at the other end. If your requirements and purposes change only slowly, and environmental change is essentially limited to either changes in volume (whether thruput or data) or the very rare new requirement rewriting will be rare (unless you need a lot of tuning because the existing code is rubbish). If every new customer brings new requirements and your applications live in a rapidly changing marketplace where rapidly evolving/changing technologies other than database are the norm and new techologies that provide new capabilities are popping up frequently you live with continuous rewrites and refactoring (and have to learn the techniques of incremental validation and release). Most people are unlucky (sorry Brandie :-D) and live between the extremes, where they get neither the predictability of the stable environment nor the excitement of the unstable one.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Geoff, this comment should have come first, but I decided to read through all existing comments first and could resist responding to a couple of them. So please accept my apologies for not making this comment my first on your mini-article.

It's a great article; very focussed, very clear and clean, and absolutely easy to understand. It addresses a real problem that people have in understanding what isNumeric does and then, when they've understood that, understanding how to do what they had hoped isNumeric would do for them. I wish I had had it a few years ago to print off and give to people - it could have saved some trouble! You've proved that Steve's idea of short tightly focussed articles was a brilliant idea, and that you are an ideal person to write such articles.

Tom

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 14

Dang, Tom. My apologies for missing your feedback on this one.

While I'm here, thank all of you again for the great posts you've all made on this subject.

And being American, 1 Pound = 1#. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

s b dragoo Old Hand Points: 347 More actions · Answer 15

Great little article. Several years ago while I still was a junior DBA, I ran into this very issue. It was quite perplexing at the time (and unfortunately involved people's property tax bills). I finally figured out the error of my ways. Your article has pointed out a couple of characters that I was not aware of still. Greatly appreciated!

Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com