ISNUMERIC(varchar)

  • Kanu, thanks for the question. While basic, it was interesting. Let's see some more.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Thanks, interesting question...

  • if "101" had been an option I'd have chosen it (and been wrong)

    I would not have expected isnumeric('$') to be 0

  • easy question..

    there is an excellent explanation from Jeff moden..

    http://www.sqlservercentral.com/articles/IsNumeric/71512/

  • pchirags (10/15/2013)


    easy question..

    there is an excellent explanation from Jeff moden..

    http://www.sqlservercentral.com/articles/IsNumeric/71512/

    +1 I was thinking about the same article pchirags. 😀 I've ran into a few issues like this with IsNumeric and have done my homework on it so this was an easy one. Great question.



    Everything is awesome!

  • pchirags (10/15/2013)


    easy question..

    there is an excellent explanation from Jeff moden..

    http://www.sqlservercentral.com/articles/IsNumeric/71512/

    Yes. That SQL Spackle is excellent as is the ensuing dicussion. Thanks for bringing it to our attention.

  • Dhruvesh Shah (10/14/2013)


    Good question. Any one know why it returns 1 for some/most of special characters?

    ISNUMERIC returns 1 if the input can be converted to a numeric data type. It doesn't bother to tell you which data type.

    In the case of '-', most data types will do (It is interpreted as -0). Similar for '.', which is interpreted as 0.0.

    The '$' symbol converts to only a specific numeric data type: money (and smallmoney). It is interpreted as $0.00.

    SELECT CAST('$' AS money);


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Nice question, thanks! 🙂

  • Hugo Kornelis (10/15/2013)


    ISNUMERIC returns 1 if the input can be converted to a numeric data type. It doesn't bother to tell you which data type.

    I've yet to discover what numeric type a non-breaking space (ASCII 160) can be converted to, though honestly I haven't put much work into it as the answer would be useless to me anyway. An ISNUMERIC(value,type) function would be far more useful.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • ronmoses (10/15/2013)


    Hugo Kornelis (10/15/2013)


    ISNUMERIC returns 1 if the input can be converted to a numeric data type. It doesn't bother to tell you which data type.

    I've yet to discover what numeric type a non-breaking space (ASCII 160) can be converted to, though honestly I haven't put much work into it as the answer would be useless to me anyway. An ISNUMERIC(value,type) function would be far more useful.

    ron

    As others have said, TRY_CONVERT in SQL Server 2012 fills that gap.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (10/15/2013)


    As others have said, TRY_CONVERT in SQL Server 2012 fills that gap.

    Ah yes, I must have glossed over it due to the "2012" part. I try not to get excited about toys I can't have. 😀

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Really simple one, back to basics... thanks, Kanu!

  • Nice and easy Q, thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • ronmoses (10/15/2013)


    I've yet to discover what numeric type a non-breaking space can be converted to

    Most of them I'd have thought, on the basis that in some locales a space is used as the thousands separator.

  • Had forgotten about this so it was a great reminder. Thanks

    Not all gray hairs are Dinosaurs!

Viewing 15 posts - 16 through 30 (of 34 total)

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