April 8, 2013 at 8:08 am
I think the short takeaway is that IsNumeric just shouldn't be trusted. It looks like a best-practice way to check that a string is a number but it contains a hidden gotcha that you'll only learn about when the data contains the edge cases (fun to debug)
I feel like this is the same class of [mis]behavior as the isnull() vs coalesce() trickery re data type
It's a good question to raise the issue. It's unfortunate that these pit-traps lie in wait for unsuspecting users.
April 8, 2013 at 8:57 am
As far as I understand, IsNumeric works roughly like this:
CASE WHEN COALESCE(
TRY_CAST(@input AS numeric)
,TRY_CAST(@input AS bigint)
,TRY_CAST(@input AS money)
,TRY_CAST(@input AS smallint)
,TRY_CAST(@input AS smallmoney)
,TRY_CAST(@input AS tinyint)
,TRY_CAST(@input AS float)
,TRY_CAST(@input AS decimal)
,TRY_CAST(@input AS real
)
) IS NULL THEN 0 ELSE 1 END
So as long as the conversion to any of the numeric data types works, it will return 1 otherwise 0.
Best Regards,
Chris Büttner
April 8, 2013 at 9:10 am
Heh - I got burnt with ISNUMERIC just today - had some dodgy character data and thought it might just work to test which records I could convert to int, but it was a no go.
The following article appeared a few months back for those who want to dig into ISNUMERIC some more: http://www.sqlservercentral.com/articles/IsNumeric/71512/
It also has a handy way to check for just numbers in a string which is what I ended up using instead of ISNUMERIC.
April 8, 2013 at 9:40 am
Christian Buettner-167247 (4/8/2013)
As far as I understand, IsNumeric works roughly like this:
CASE WHEN COALESCE(TRY_CAST(@input AS numeric)
,TRY_CAST(@input AS bigint)
,TRY_CAST(@input AS money)
,TRY_CAST(@input AS smallint)
,TRY_CAST(@input AS smallmoney)
,TRY_CAST(@input AS tinyint)
,TRY_CAST(@input AS float)
,TRY_CAST(@input AS decimal)
,TRY_CAST(@input AS real
)
) IS NULL THEN 0 ELSE 1 END
So as long as the conversion to any of the numeric data types works, it will return 1 otherwise 0.
That is my understanding as well. Unfortunately it doesn't provide any feedback as to which conversion actually worked, which in my opinion makes it a rather useless function.
April 8, 2013 at 12:36 pm
angad.singh88 (4/8/2013)
Thanks for the most obvious answer to turn out to be something not known. 🙂Thanks to Hugo for his explanation.
Angad
+1, with thanks to Gary.
April 8, 2013 at 1:26 pm
Revenant (4/8/2013)
angad.singh88 (4/8/2013)
Thanks for the most obvious answer to turn out to be something not known. 🙂Thanks to Hugo for his explanation.
Angad
+1, with thanks to Gary.
+1
Thanks
April 10, 2013 at 6:39 am
kapil_kk (4/8/2013)
Hugo Kornelis (4/8/2013)
kapil_kk (4/8/2013)
For the first part I understand the behavior but am still confused with the SELECT ISNUMERIC('.+') -- Returns 0 behavior 🙁The . actually represents 0.0, with leading and trailing zero omitted.
So +. is equivalent to +0.0, which is a valid number. But .+ is equivalent to 0.0+, which is not. The plus sign (just as the minus sign) is only valid at the start of the numeric string.
I am again surprised with these two statements behavior:
SELECT ISNUMERIC('.,') -- Return 1
SELECT ISNUMERIC(',.') -- Return 1
The comma is used as the thousands seperator in America (and, as far as I know, most English speaking countries). When converting from string to money and smallmoney, thousands seperators in the string are accepted. This is implemented in a rather loose way - there is no checkk if the thousands seperators are where they should be, they are simply ignored.
Thanks Hugo, now its clear to me :-):-)
+1... nice explanation...
Manik
You cannot get to the top by sitting on your bottom.
June 9, 2015 at 12:25 am
This is a funny QOTD.
The only one I didn't get was the 9th.
Is \, numeric ?
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply