ISNUMERIC ISSTUPID (and ISGONNAGETITSASSKICKED if it isn't careful)

by Michael S. Kaplan, published on 2007/12/12 10:16 -05:00, original URI: http://blogs.msdn.com/b/michkap/archive/2007/12/12/6731510.aspx


I had a regular1 reader the other day tell me that I needed to stop writing about SQL Server so much. Since this person is on the SQL Server team, there is probably some subtext here that I am missing. But I think this is the last one I have on tap for the moment2. :-)

In relationships, there is a truism that it is not in a person's best interest to be markedly happier than the least happiest person in the relationship.

You may find in the long run that the delta between the two happiness levels is a material force that is expended as energy used to help equalize the two, usually by bringing down the happier of the two....

Now T-SQL functions in SQL Server lack self awareness, but they do have a trait best thought of as intelligence.

Which is to say, it is beyond them to be sentient, but it is not beyond them to be stupid.

The other day I had someone send me email asking if I had seen the article from the Connect site entitled Enhancement to ISNUMERIC with the following description:

ISNUMERIC validates against a wide range of numeric formats so a return value of 1 (True) does not guarantee that CONVERT(type,string) will work for every numeric type. For example:

ISNUMERIC(',100,00.25') returns 1 but CONVERT(type,',100,00.25') and fails when type is INT or FLOAT but succeeds when type is MONEY.

Elsewhere in the feedback on this site, and on the internet, similar problems have been reported - eg with strings such as '10.20D-30' for which ISNUMERIC returns 1 and CONVERT works when type is FLOAT but fails when type is INT.

This behaviour has been observed on SQL Server 2000, MSDE and SQL Server 2005 Express.

A very good point, really.

I'll reword it a bit, though....

As currently implemented, ISNUMERIC really ISSTUPID.

Now this is different than VBScript's IsNumeric function and Visual Basic's IsNumeric function.

They are not rocket scientists either -- in kind of the same way.

In each case, the function shares validation with every single possible numeric or somewhat numeric datatype, and if any one or more of them are valid, the function will return TRUE (or 1 in the ISNUMERIC case).

So if they all behave the same way, why would I call the T-SQL function stupid while not doing the same with the VB/VBS functions?

Because in the case of VB/VBS functions, there is the fact that all of the actual conversions one might subsequently use to convert the data to a number often tend to be just as stupid as their IsNumeric counterparts, and thus the stupidity of the like methods are all equal.

It's like if you hang out with Einstein, you probably aren't Homer Simpson (though if you are hanging out with the gang at Moe's, being Homer Simpson might be just fine!).

It is just common sense -- if one is going to be stupid, make sure those who depend on you are just as stupid and neither one of you will be unhappy with the other!

So in the long run, a function that does nothing other than saying "this could be shoved into some kind(s) of numbers but which or how many is not something I will tell you", if it were sentient, would be the kind of function that would get its ass kicked by a lot of data that passed by.

But since it is not sentient, we'll just call ISNUMERIC stupid....

Now I have to say the proposed solution is not one that has any chance of exciting me. :-(

Add an optional type parameter to ISNUMERIC so a developer can easily test for a specific format number prior to applying the CAST or CONVERT function to it. This not only avoids developers having to write their own equivalent function, but also alerts them to these problems with the current implementation where all possible numeric formats are acceptable.

Hence:

    ISUMERIC(',100,00.20',INT) would return 0 and
    ISUMERIC(',100,00.20',MONEY) would return 1 and

If the optional type parameter was omitted the current behaviour would remain to ensure back-ward compatability.

Until this is implemented, it would be good if some explanatory examples and notes could be added to the documentation to alert developers to these situations.

In my opinion, this is not really stellar (I mean the suggested fix, not the documentation) -- if you knew the type or types you wanted to test and were willing to try them serially, then you can pretty much do it yourself with CAST or CONVERT calls and look at the error levels. Nothing special needed and this is mere syntactic sugar.

Why not a function that either returns a flag value with the most restrictive numeric type that can be supported or alternately a bitmask of those flags containing all the types that the expression could fit into?

Now one of those ideas might be a cool enhancement to consider.

In the sentience test, if you ask a function "is this numeric?" and it tells you "yes, for INT and up" or "yes, but only for MONEY and SMALLMONEY" or "yes, for everything but UINT" then it is unlikely to be considered stupid. It also seems quite eager to help like a little puppy dog or something.

And it is unlikely to get its ass kicked by all of the data passing by.... :-)

 

1 - And when I say regular, I mean irregular. But in a nice way....
2 - You're welcome.

 

This post brought to you by(U+20a1, aka COLON SIGN)


# ReallyEvilCanine on 12 Dec 2007 11:32 AM:

The first eight words (plus the footnote) had me hoping my question would finally be answered. Meh. Does ISSNUMERIC recognise the daiji 壱 弐 and 参 ("formal numbers" for 1, 2 and 3)? If so, are other national number sets and exceptions recognised?

# Michael S. Kaplan on 12 Dec 2007 11:50 AM:

No, none of them are, at least not in SQL Server or COM or .NET....

# Dean Harding on 12 Dec 2007 4:07 PM:

"usually by bringing down the happier of the two...."

Feeling cynical? ;)

# Michael S. Kaplan on 12 Dec 2007 4:08 PM:

Always!

# Ruben on 12 Dec 2007 4:27 PM:

Unfortunately, there is no way to catch errors when trying to convert numbers when you're using CAST/CONVERT as part of a DML statement such as SELECT. Granted, you've got an ugly database when you really need to do such a thing, but still, the world ain't perfect. For example: select all records where the field presumed to be a number isn't. You need a functioning ISNUMERIC for that. :-(

# John Cowan on 12 Dec 2007 4:53 PM:

Would it be too much to ask why the bleeding H a money value is even allowed to have a leading comma, or is that just one of those financial mysteries that allows Hollywood films to gross zillions of dollars and yet show no net profits?

# Michael S. Kaplan on 12 Dec 2007 11:03 PM:

Good question John -- I'm not sure. Probably some backcompat thing they are stuck with now?

# Michael S. Kaplan on 12 Dec 2007 11:04 PM:

Hey Ruben -- you could always wrap it with an sproc and call that from the SELECT....


Please consider a donation to keep this archive running, maintained and free of advertising.
Donate €20 or more to receive an offline copy of the whole archive including all images.

go to newer or older post, or back to index or month or day