ISNUMERIC function

  • Make an Impact

    SSC-Addicted

    Points: 433

    Comments posted to this topic are about the item ISNUMERIC function

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    Nice, simple question to end the week on, thanks Kiran

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • peter.row

    SSCarpal Tunnel

    Points: 4307

    I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    peter.row - Friday, September 7, 2018 1:10 AM

    I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.

    which is where TRY_CONVERT comes in very handy

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Rune Bivrin

    SSCertifiable

    Points: 7855

    Interestingly enough, the referenced article doesn't really say anything about spaces. I'd even go so far as to say the article is just about as useless as the function itself...


    Just because you're right doesn't mean everybody else is wrong.

  • Sean Lange

    SSC Guru

    Points: 286536

    Rune Bivrin - Friday, September 7, 2018 4:13 AM

    Interestingly enough, the referenced article doesn't really say anything about spaces. I'd even go so far as to say the article is just about as useless as the function itself...

    Indeed!!! Here is an excellent spackle article on this very function. http://www.sqlservercentral.com/articles/71512/

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • peter.row

    SSCarpal Tunnel

    Points: 4307

    Stewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AM

    peter.row - Friday, September 7, 2018 1:10 AM

    I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.

    which is where TRY_CONVERT comes in very handy

    Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.

  • Sean Lange

    SSC Guru

    Points: 286536

    peter.row - Friday, September 7, 2018 8:17 AM

    Stewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AM

    peter.row - Friday, September 7, 2018 1:10 AM

    I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.

    which is where TRY_CONVERT comes in very handy

    Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.

    Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • peter.row

    SSCarpal Tunnel

    Points: 4307

    Sean Lange - Friday, September 7, 2018 8:21 AM

    peter.row - Friday, September 7, 2018 8:17 AM

    Stewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AM

    peter.row - Friday, September 7, 2018 1:10 AM

    I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.

    which is where TRY_CONVERT comes in very handy

    Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.

    Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!

    Think importing of data into holding table, which you then validate reject data that is bad and transfer to final tables that is good. This is not a 1 off and is something potentially kicked off by user or a web service etc...
    Hmmm, probably didn't think of this because we still have to support SQL 2008 R2 where TRY_CONVERT() doesn't exist.

  • Sean Lange

    SSC Guru

    Points: 286536

    peter.row - Friday, September 7, 2018 8:29 AM

    Sean Lange - Friday, September 7, 2018 8:21 AM

    peter.row - Friday, September 7, 2018 8:17 AM

    Stewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AM

    peter.row - Friday, September 7, 2018 1:10 AM

    I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.

    which is where TRY_CONVERT comes in very handy

    Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.

    Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!

    Think importing of data into holding table, which you then validate reject data that is bad and transfer to final tables that is good. This is not a 1 off and is something potentially kicked off by user or a web service etc...
    Hmmm, probably didn't think of this because we still have to support SQL 2008 R2 where TRY_CONVERT() doesn't exist.

    But why would that prevent using TRY_CONVERT? It returns NULL when it can't be converted. Seems like the perfect time to use it to me. You insert only those rows where TRY_CONVERT is not null, every row that is NULL failed.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • peter.row

    SSCarpal Tunnel

    Points: 4307

    Sean Lange - Friday, September 7, 2018 8:43 AM

    peter.row - Friday, September 7, 2018 8:29 AM

    Sean Lange - Friday, September 7, 2018 8:21 AM

    peter.row - Friday, September 7, 2018 8:17 AM

    Stewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AM

    peter.row - Friday, September 7, 2018 1:10 AM

    I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.

    which is where TRY_CONVERT comes in very handy

    Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.

    Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!

    Think importing of data into holding table, which you then validate reject data that is bad and transfer to final tables that is good. This is not a 1 off and is something potentially kicked off by user or a web service etc...
    Hmmm, probably didn't think of this because we still have to support SQL 2008 R2 where TRY_CONVERT() doesn't exist.

    But why would that prevent using TRY_CONVERT? It returns NULL when it can't be converted. Seems like the perfect time to use it to me. You insert only those rows where TRY_CONVERT is not null, every row that is NULL failed.

    See the bit where I said we still have to support SQL 2008R2 - TRY_CONVERT() does not exist in SQL 2008R2 and we haven't got the time and resource to support multiple versions of what should be the same thing.
    Hopefully we will drop support for 2008 R2 soon and hence new TSQL syntax will be available for us to improve things.

  • Shayn Thomas

    SSCertifiable

    Points: 5637

    nice and easy question, cheers

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • Alan Burstein

    SSC Guru

    Points: 61087

    peter.row - Friday, September 7, 2018 8:50 AM

    Sean Lange - Friday, September 7, 2018 8:43 AM

    peter.row - Friday, September 7, 2018 8:29 AM

    Sean Lange - Friday, September 7, 2018 8:21 AM

    peter.row - Friday, September 7, 2018 8:17 AM

    Stewart "Arturius" Campbell - Friday, September 7, 2018 2:28 AM

    peter.row - Friday, September 7, 2018 1:10 AM

    I got caught out. No one I know uses IsNumeric() to check if a string is numeric because there are many cases where it says true where if you attempt to store the value in an actual numeric type it fails.

    which is where TRY_CONVERT comes in very handy

    Not very handy when you're trying to do a set based operation on a table of strings that should be numbers though.

    Why? It can be used in a set based approach. But really the problem here is the table of strings that should be numbers!!!

    Think importing of data into holding table, which you then validate reject data that is bad and transfer to final tables that is good. This is not a 1 off and is something potentially kicked off by user or a web service etc...
    Hmmm, probably didn't think of this because we still have to support SQL 2008 R2 where TRY_CONVERT() doesn't exist.

    But why would that prevent using TRY_CONVERT? It returns NULL when it can't be converted. Seems like the perfect time to use it to me. You insert only those rows where TRY_CONVERT is not null, every row that is NULL failed.

    See the bit where I said we still have to support SQL 2008R2 - TRY_CONVERT() does not exist in SQL 2008R2 and we haven't got the time and resource to support multiple versions of what should be the same thing.
    Hopefully we will drop support for 2008 R2 soon and hence new TSQL syntax will be available for us to improve things.

    I agree with Sean that TRY_CONVERT would be fine for what you're doing (except that it's not available in 2008.) ISNUMERIC can be fine if you understand what it does as well as the data you're querying but I never use it. For what you're describing - it sounds like you can also eadily get the job done using PATINDEX or LIKE.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 13 posts - 1 through 13 (of 13 total)

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