Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

  • Comments posted to this topic are about the item Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Great spackle Jeff! Thanks for putting this brief article together - an easy and informative read.

    cheers,

    Steve.

  • Simple and nice article,

    some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

    CREATE FUNCTION dbo.isReallyNumeric

    (

    @num VARCHAR(64)

    )

    RETURNS BIT

    BEGIN

    IF LEFT(@num, 1) = '-'

    SET @num = SUBSTRING(@num, 2, LEN(@num))

    DECLARE @pos TINYINT

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

    RETURN CASE

    WHEN PATINDEX('%[^0-9.-]%', @num) = 0

    AND @num NOT IN ('.', '-', '+', '^')

    AND LEN(@num)>0

    AND @num NOT LIKE '%-%'

    AND

    (

    ((@pos = LEN(@num)+1)

    OR @pos = CHARINDEX('.', @num))

    )

    THEN

    1

    ELSE

    0

    END

    END

    GO

  • Good stuff Jeff. Got that spackle article out pretty quick.

    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

  • sharath.chalamgari (11/30/2010)


    Simple and nice article,

    some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

    This poster brings up a good point: "-" and ".".


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • stevefromOZ (11/30/2010)


    Great spackle Jeff! Thanks for putting this brief article together - an easy and informative read.

    cheers,

    Thanks for the positive feedback, Steve. I called these short articles "SQL Spackle" for the reason given. The concept of these short, single point, get to the point articles was Steve Jone's idea. I was a little worried that some folks would look at these a snub a nose with "Pffft! Already knew that and the article is too short!". That's why we had Phil McCracken (pen-name suggested by Paul White) do a lead in on each of these articles. To make them easy to find, Steve created a new keyword lookup for the word "spackle".

    Again, thanks for taking the time to provide your feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sharath.chalamgari (11/30/2010)


    Simple and nice article,

    some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

    Thanks for the feedback and the code example, Sharath. That's one of the really good things about having the "discussion area" even on short articles. Goodies like what you posted come right out.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • autoexcrement (11/30/2010)


    sharath.chalamgari (11/30/2010)


    Simple and nice article,

    some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

    This poster brings up a good point: "-" and ".".

    If you're looking for valid numbers, I agree. If you're looking for an "IsAllDigits" function, you wouldn't want to include those two characters.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • CirquedeSQLeil (11/30/2010)


    Good stuff Jeff. Got that spackle article out pretty quick.

    Thanks, Jason. Heh... When Steve first came out with the "Requested Articles" forum, I banged out 2 "spackles" that night and 2 more the next night. From the looks of the expected publish dates he's assigned on my contribution page, it looks like he has me scheduled every Wednesday for about 4 weeks. The first one came out on Nov 15th.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/30/2010)


    autoexcrement (11/30/2010)


    sharath.chalamgari (11/30/2010)


    Simple and nice article,

    some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

    This poster brings up a good point: "-" and ".".

    If you're looking for valid numbers, I agree. If you're looking for an "IsAllDigits" function, you wouldn't want to include those two characters.

    yes we were looking at the valid numbers and used this function.

    i got this function from the below URL

    http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html">

    http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

  • Jeff Moden (11/30/2010)


    CirquedeSQLeil (11/30/2010)


    Good stuff Jeff. Got that spackle article out pretty quick.

    Thanks, Jason. Heh... When Steve first came out with the "Requested Articles" forum, I banged out 2 "spackles" that night and 2 more the next night. From the looks of the expected publish dates he's assigned on my contribution page, it looks like he has me scheduled every Wednesday for about 4 weeks. The first one came out on Nov 15th.

    I must have missed it. What is the link?

    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

  • Thanks Jeff for this nice and very well explained article.

  • autoexcrement (11/30/2010)


    sharath.chalamgari (11/30/2010)


    Simple and nice article,

    some time back i was working on validating a column in the database and i came across this issue and we had this function to validate.

    This poster brings up a good point: "-" and ".".

    I have used a similar function but I had to make sure that the string could be converted into a float.

    So there must be additional checks:

    "-" and "+" only at the first position

    "." allowed only once

    TestCases:

    select cast('+1.0'as float) -- OK

    select cast('-1.0'as float) -- OK

    select cast('1.0-'as float) -- NOK

    select cast('1.0+'as float) -- NOK

    select cast('.5' as float) -- OK

    select cast('5.' as float) -- OK

    select cast('5.5.5' as float) -- NOK

  • Very nice article. Well written and now added to my bookmarks.

    Thanks for sharing

    David Bridge

    http://www.DavidBridgeTechnology.com

    David Bridge
    David Bridge Technology Limited
    www.davidbridgetechnology.com

  • Even people who think they know should read this article. I actually knew all about the punctuation and currency signs, but then I got to the "e" and "d" part. DOH. Hadn't run into that issue before. Mentioning scientific notation = Good Idea.

    Great article. Thumbs up.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 15 posts - 1 through 15 (of 168 total)

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