IsNumeric with NULL and Empty String

  • 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

  • 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.

  • Good question!!

  • 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.

  • IsNumeric - sigh.

    Thank goodness for the improvements in the next version of SQL Server like TRY_CONVERT.

  • 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.

  • 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

  • Nice question.

    -----------------
    Gobikannan

  • nice!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Good question. Reviewed some basic concepts. Obviously, I needed this refresher because I got it right for the wrong reason, too.

  • Nice question. Simple and makes a good point on Conversions.

  • 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.

  • 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 😉

  • 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 🙂

  • 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