August 4, 2011 at 7:11 am
Nice. But try it.
DECLARE @NumericKey INT
SET @NumericKey = NULL
SELECT ISNULL(@NumericKey, 0), IsNumeric(@NumericKey),@NumericKey
SET @NumericKey = ''
SELECT ISNULL(@NumericKey, 0), IsNumeric(@NumericKey),@NumericKey
GO
DECLARE @NumericKey VARCHAR(30)
SET @NumericKey = NULL
SELECT ISNULL(@NumericKey, 0), IsNumeric(@NumericKey),@NumericKey
SET @NumericKey = ''
SELECT ISNULL(@NumericKey, 0), IsNumeric(@NumericKey),@NumericKey
August 4, 2011 at 7:42 am
Tom.Thomson (8/4/2011)
I've always found it a bit disappointing that IsNumeric(Null) doesn't return NULL; after all, a NULL value doesn't contain any non-numeric characters, so it's not not numeric; and it doesn't contain any characters that are numeric, so it's not numeric either; so it should surely return NULL; just yet another case where (T-)SQL gets it wrong with NULL. But then IsNumeric is such an awful function anyway that that's teh least of its worries (perhaps the biggest is its name).
Thanks for that - I've learned something new today!
I always thought that the justification for things like '+' or '$' returning 1 for isNumeric was that it was possible to assign them to at least one numeric datatype, in this case money. But on that basis, shouldn't IsNumeric(NULL) also return 1, since NULL can be assigned to any numeric datatype? I'd have guessed at it returning NULL, but to return 0 is just bizarre.
August 4, 2011 at 8:11 am
Good question!!
August 4, 2011 at 8:15 am
Thomas Abraham (8/4/2011)
Britt Cluff (8/4/2011)
Looks like mostly good feedback. Glad most people have had a positive experience today.Wait for it. There will be someone that will hold a baby coyote in their left hand, a copy of a NYC subway map in their right, chant the names of the last 5 UN Secretary Generals, and manage to get a different result. 😉
None of which will change my appreciation for a great question. Thanks. Count me as a "got it right for wrong reason" today. Good to be reminded of simple things like implicit conversion. I usually catch it in my own code, but maintain lots of code written by unseen developers.
Great comment. I'm just glad there wasn't an 'It Depends' as a possible answer.
August 4, 2011 at 8:41 am
IsNumeric - sigh.
Thank goodness for the improvements in the next version of SQL Server like TRY_CONVERT.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 4, 2011 at 9:06 am
SQLkiwi (8/4/2011)
IsNumeric - sigh.Thank goodness for the improvements in the next version of SQL Server like TRY_CONVERT.
TRY_CONVERT - sigh.
While it's in many ways better than IsNumeric, TRY_CONVERT does have some problems. Take this example from the documentation:
SELECT
CASE WHEN TRY_CONVERT(float,'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result
Now try:
SELECT
CASE WHEN TRY_CONVERT(float,NULL) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result
Did the second cast fail? You don't really know, because NULL is returned when the cast fails, but also when the cast succeeds and the result of the cast is NULL.
August 4, 2011 at 9:35 am
Thanks for the question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 4, 2011 at 11:07 am
Nice question.
-----------------
Gobikannan
August 4, 2011 at 11:35 am
nice!
August 4, 2011 at 12:01 pm
Good question. Reviewed some basic concepts. Obviously, I needed this refresher because I got it right for the wrong reason, too.
August 4, 2011 at 12:03 pm
Nice question. Simple and makes a good point on Conversions.
August 4, 2011 at 12:10 pm
Daniel Bowlin (8/4/2011)
Implicit conversion is one of those issues, where if you know it, you just know. If you don't know it, the result is very unexpected. I am learning more and more of these, but I wonder, is there a singular document reference that lists all the implicit conversions that happen in SQL Server?
There absolutely is!
http://msdn.microsoft.com/en-us/library/ms187928.aspx
It lists all the conversions that can happen Implicitly and those that can not.
August 4, 2011 at 12:18 pm
Tom.Thomson (8/4/2011)
just yet another case where (T-)SQL gets it wrong with NULL. But then IsNumeric is such an awful function anyway that that's teh least of its worries (perhaps the biggest is its name).
IsNumeric is a function that was included into (T)SQL from other Languages that "Get It Wrong" the same way.
Very similar to STUFF 😉
August 4, 2011 at 12:20 pm
sknox (8/4/2011)
While it's in many ways better than IsNumeric, TRY_CONVERT does have some problems.
Oddly enough, this is exactly the stance I took a week or so ago in a discussion elsewhere. The simple answer is to add a test for the item being converted being NULL.
It's difficult to add to your example, because it uses a constant, but in real-world applications we will need to be careful to distinguish between the NULL meaning convert-failure, and the NULL meaning input-was-NULL.
In the end, I was convinced that this was a non-issue, largely because the alternatives were worse (e.g. a can-I-convert-this function). I still think it will catch people out, but I am in the minority 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 4, 2011 at 12:29 pm
SQLkiwi (8/4/2011)
sknox (8/4/2011)
While it's in many ways better than IsNumeric, TRY_CONVERT does have some problems.Oddly enough, this is exactly the stance I took a week or so ago in a discussion elsewhere. The simple answer is to add a test for the item being converted being NULL.
It's difficult to add to your example, because it uses a constant, but in real-world applications we will need to be careful to distinguish between the NULL meaning convert-failure, and the NULL meaning input-was-NULL.
In the end, I was convinced that this was a non-issue, largely because the alternatives were worse (e.g. a can-I-convert-this function). I still think it will catch people out, but I am in the minority 🙂
I have to agree. Every time I look at things like this my brain starts repeating the following mantra:
"Code to enforce Business Rules should not be in SQL!"
Validation of user interfact data is best done by application Functions or Subroutines, not DML. 😎
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply