SQL Server Performance

Problem with ISNUMERIC

Discussion in 'General Developer Questions' started by vietcave, Sep 8, 2005.

  1. vietcave New Member

    Hi everyone,

    I find out the problem with ISNUMBERIC.

    SELECT ISNUMERIC('1010') ---> 1: exactly

    SELECT ISNUMERIC('10C0') ---> 0: exactly

    SELECT ISNUMERIC('10E0') ---> 1: wrong

    Why? Is there something wrong when E appear?

    Thanks

    vietcave, Sep 8, 2005
    #2

  2. satya Moderator

    satya, Sep 8, 2005
    #2

  3. Madhivanan Moderator

    Yes
    Better to use Like '%[0-9]%' than IsNumeric

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Sep 8, 2005
    #2

  4. FrankKalis Moderator

    Don't rely on ISNUMERIC!

    The d(D) and e(E) are used in Fortran to denote a float double precision (d), respectively exponential writing (e). SQL Server internally uses the standard C++ function ATOF() to parse such strings. When you look at the definition of ATOF() it seems to follow the Fortran rules.
    '10E0' is interpreted as FLOAT and will yield 10.0. It's a shortcut to 10 * 10^0.
    If you need to be sure about your input either use a client language (which are far better suited for such stuff) or use PATINDEX().

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, Sep 8, 2005
    #2

  5. Madhivanan Moderator

    and also Select IsNumeric('1,100') will return 1
    So using Like is the best approach to check for numerics

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Sep 8, 2005
    #2

  6. vietcave New Member

    Thank all of you very much.
    vietcave, Sep 8, 2005
    #2

  7. qd59 New Member

    Here is my Solution: I made a stored procedure to deal with the numeric problem, you can try.
    It will return 0 for non-numeric and 1 for numeric.

    --********************************************************
    ALTER PROCEDURE usp_CheckNumeric (@DATATOCHECK NVARCHAR (1000))
    AS
    DECLARE @CHEKRE INT
    DECLARE @TTS NVARCHAR (1000)
    DECLARE @POS INT
    DECLARE @POSCHAR NVARCHAR (1)
    --SET @POS = 1
    SET @TTS = @DATATOCHECK
    SET @CHEKRE = 1
    IF LEN (@TTS)=0 OR @TTS IS NULL
    BEGIN
    SET @CHEKRE = 0
    END
    ELSE
    BEGIN
    IF CHARINDEX ('-',@TTS) = 1
    BEGIN
    SET @POS =2
    END
    ELSE
    BEGIN
    SET @POS =1
    END

    --PRINT @POS
    WHILE @POS <= LEN (@TTS) AND @CHEKRE<>0
    BEGIN

    SELECT @POSCHAR = RIGHT(LEFT(@TTS, @POS),1)
    --PRINT @POSCHAR
    --PRINT ASCII(@POSCHAR)
    IF @POSCHAR='.'
    BEGIN
    IF CHARINDEX ('.',@TTS) = 1 OR CHARINDEX ('.',@TTS) = LEN(@TTS)
    BEGIN
    SET @CHEKRE = 0
    --PRINT 'ERROR HERE'
    END
    END
    ELSE
    BEGIN
    SET @POSCHAR = REPLACE(@POSCHAR,'.',0)
    IF ASCII(@POSCHAR) NOT IN (48,49,50,51,52,53,54,55,56,57) OR @POSCHAR=' '
    BEGIN
    SET @CHEKRE = 0
    END
    ELSE
    BEGIN
    SET @CHEKRE = 1
    END
    END

    SET @POS = @POS + 1
    END
    --END
    END
    RETURN @CHEKRE
    GO
    qd59, Sep 28, 2005
    #2

  8. FrankKalis Moderator

    Looks a bit like an overkill to me, but if it's working...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
    FrankKalis, Sep 29, 2005
    #2

  9. Madhivanan Moderator

    But It seems like using Cursors<br /><br />Where col not like '%[a-zA-Z]%' -- and other characters <br />will work I think [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
    Madhivanan, Sep 29, 2005
    #2

  10. nalaka New Member

    here is another instance where isnumeric function fails.
    select isnumeric('+')
    select isnumeric('-')

    both cases returns 1

    better to go with wildcard approach
    nalaka, Apr 18, 2007
    #2

Share This Page