SQL Server Performance

ISNumeric()

Discussion in 'General DBA Questions' started by CanadaDBA, Oct 26, 2004.

  1. CanadaDBA New Member

    My developers had a trouble using ISNumeric(). ISNumeric('$500') returns TRUE while they want to return FALSE. BOL says ISNumeric()returns true for all kind of numerics and money. I can write a function to check if there are only numbers and period. Is there a better solution?

    CanadaDBA
  2. CanadaDBA New Member

    A friend gave an interesting solution:

    quote:Originally posted by robvolk

    SELECT CASE WHEN '$500' LIKE '%[^0-9.]%' THEN 'Not Numeric' ELSE 'Is Numeric' END
    But I couldn't undrestand how LIKE '%[^0-9.]%' works. What's the trick behind that?


    CanadaDBA
  3. Adriaan New Member

    It's a fairly regular LIKE expression:

    ... I'll just skip the % wildcards...

    The square brackets [ and ] mean that we're looking for a match in a range of characters. However, the ^ sign at the beginning of the range is the negative version, so we'll get a match if there's a character that is NOT in the given range.

    By the way, the period should NOT be supplied in the range of characters, because periods ARE allowed.
    My bad.
  4. CanadaDBA New Member

    As I have found it is working like "If there is a character that is not in the range of 0 to 9" OR "is period". Therefore, it returns TRUE for 500.00 but FALSE for $500



    CanadaDBA
  5. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FarhadR</i><br /><br />As I have found it is working like <i>"If there is a character that is not in the range of 0 to 9"</i> OR <i>"is period"</i>. Therefore, it returns TRUE for 500.00 but FALSE for $500<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I think my correction to my previous post pointed out the same issue.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  6. CanadaDBA New Member

    Yeh, I saw your correction after I sent you my post.
    Thanks Adriaan,

    CanadaDBA
  7. Twan New Member

    however it thinks that 500.0.0.0 is a valid number when it is not... and that 50e+2 is not a valid number when it is

    Cheers
    Twan
  8. Adriaan New Member

    Okay Twan, you win -

    DECLARE @Amnt VARCHAR(100)

    SET @Amnt = '500.0.0.0'

    SELECT CASE WHEN (@Amnt LIKE '%[^0-9.]%')
    OR (LEN(@Amnt) - LEN(REPLACE(@Amnt, '.', '')) > 1)
    OR @Amnt = '.' OR @Amnt = '' OR @Amnt IS NULL
    THEN 'Not Numeric' ELSE 'Is Numeric' END
  9. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Twan</i><br /><br />however it thinks that 500.0.0.0 is a valid number when it is not... and that 50e+2 is not a valid number when it is<br /><br />Cheers<br />Twan<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Twan,<br /><br />You obviously have a version that also covers pi and the square root of -1.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  10. CanadaDBA New Member

    I used the following code instead of using ISNumeric():


    DECLARE @Str VarChar(50)

    SELECT CASE WHEN @Str LIKE '%[^0-9.]%'
    THEN 0
    ELSE IsNumeric(@Str)
    END

    Thanks for postings,

    CanadaDBA
  11. Twan New Member

    and infinity... <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Twan
  12. Adriaan New Member

    CanadaDBA,

    No need to do ISNUMERIC() after the LIKE - if the expression does not match the criteria, then all the characters in the expression are acceptable. If that is the case, then the result of ISNUMERIC(@Str) is always 1.

    You can actually also use the LIKE keyword when controlling the flow of a procedure, like this:


    IF (@Str LIKE '%[^0-9.]%')
    BEGIN
    -- respond to incorrectly formatted parameter
    END
  13. CanadaDBA New Member

    The ELSE part takes care of '500.0.00' cases and returns 0 which means it is not a number.

    For my case, some numbers come with $ sign and I want to control them.


    quote:Originally posted by Adriaan

    ...
    No need to do ISNUMERIC() after the LIKE - if the expression does not match the criteria, then all the characters in the expression are acceptable. If that is the case, then the result of ISNUMERIC(@Str) is always 1.
    ...

    CanadaDBA
  14. Twan New Member

    should it also cater for negative numbers?

    maybe something like

    isnumeric( @str ) = 1
    and @str like replicate( '[-+0-9.eE]', datalength( @str ) )

    Cheers
    Twan
  15. CanadaDBA New Member

    Good point! Also you are demonstarting another usage of LIKE. I mean you have removed % and used REPLICATE. Interesting!
    I should check it tomorrow.

    quote:Originally posted by Twan

    should it also cater for negative numbers?

    maybe something like

    isnumeric( @str ) = 1
    and @str like replicate( '[-+0-9.eE]', datalength( @str ) )

    Cheers
    Twan

    CanadaDBA
  16. benwilson New Member

    Just wondering why datalength was used in the following:

    quote:isnumeric( @str ) = 1
    and @str like replicate( '[-+0-9.eE]', datalength( @str ) )
    I have been playing around with this and had to use len(@str)

Share This Page