Post reply

IS NUMERIC OUTPUT

  • April 20, 2007 at 10:21 am

    #173728

    Below is the code if i run this on SQL 2000 & 2005 i get different output, can any one tell me why is it so?
     
    IF ISNUMERIC(SUBSTRING('VDHL1PY\117',8,2))= 1
     BEGIN
       PRINT 'IS NUMERIC'
     END
    ELSE
     PRINT 'NOT NUMERIC'

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • April 20, 2007 at 10:53 am

    #701778

    There doesn't seem to be much rhyme nor reason to the isnumeric() function. I don't have SQL Server 2000 nearby to test it, but in 2005 I ran this script and saw some very strange "numeric" characters indeed:

    declare

    @x int; set @x = 0

    while

    @x < 256

    begin

    set @x = @x + 1; if isnumeric(char(@x)) = 1 print char(@x)

    end

    It has been suggested elsewhere that there be a variable for the isnumeric() function to indicate a specific data type. So isnumeric('$', 'int') would return 0 while isnumeric('$', 'money') would return 1. One can dream... !

    Ron Rice

     

  • April 20, 2007 at 11:01 am

    #701780

    It seems that "\" has some strange (and undocumented) characteristic in 2005. It seems to turn it into a float or numeric.

    Try:

    SELECT 1

    and

    SELECT \1

    in a query window, and you'll see what I mean. It doesn't do the same in 2000.

    That answers the question as to why the two versions return different results for your query, but it doesn't answer why that behavior is there in the first place.

  • April 20, 2007 at 11:11 am

    #701781

    IsNumeric is very different that IsAllNumbers.  Here's the best way I know how to come to that conclusion (IsAllNumbers)

     

    SELECT CASE WHEN '1/1' NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END AS IsAllNumbers

    SELECT CASE WHEN '11' NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END AS IsAllNumbers

  • April 20, 2007 at 12:13 pm

    #701787

    I had a function in 2000 which use to substring & convert values from varchar column, now this is failing in 2005 because its excepts the "\" char as number but when i assign it int it fails.

    I think i need to rewrite the logic considering this issue.

     

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • April 20, 2007 at 12:50 pm

    #701798

    Prakash, Isnumeric (the T-SQL version) has always been wonky, and many of us have written customized routines to deal with our specific issues because of that. You'll likely find a ton of them on the web due to this, so you don't necessarily have to start from scratch.

  • April 20, 2007 at 12:58 pm

    #701800

    David i agree with you on this, even i have customized my function do deal with this issue.

    Thank you all very much for your suggestions.

     

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

Viewing 7 posts - 1 through 6 (of 6 total)

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