Post reply

How to determine whether string is a number?

  • October 10, 2003 at 7:04 pm

    #107212

    I need to determine whether the string '4d4' is number or not. But it appears that the ISNUMERIC function is not always accurate as shown below:

    SELECT ISNUMERIC(SUBSTRING('4D4',1,3));

    It should evaluate to 0 but it returns 1! You can try for yourself.

    Basically, I need a way to determine that '4D4' is not a number. Anyone have any ideas?

    Thanks in advance,

    Billy

    Edited by - bp on 10/10/2003 7:04:09 PM

    Edited by - bp on 10/10/2003 7:04:22 PM

  • October 10, 2003 at 7:31 pm

    #477940

    SQL Server is able to evaluate that as a number, as it can use 4D4 (synonym, for some reason, of 4E4, AKA 4E+4 or 4*10^4) as a number. Try this:

    

    SELECT ISNUMERIC('4D4'), CAST('4D4' AS float)

    As you're apparently wanting to test a substring of known length, you could try something like this:

    

    SELECT CASE WHEN SUBSTRING('4D4',1,3) LIKE '[0-9][0-9][0-9]' THEN 1 ELSE 0 END

    Perhaps some of our non-US members know why '4d4' should evaluate to 4000.

    --Jonathan



    --Jonathan

  • October 10, 2003 at 7:40 pm

    #477941

    Thanks Jonathan.

    I actually never thought that 4d4 would evaluate to a number. For me, '4d4' evaluates to only 4. Totally bizarre. I wonder if it is a bug or hidden feature?

    My intention is to use a function that will determine whether a list of values I obtain from varchar column (don't ask why a number is stored in varchar field) is in numeric format because I need to append that number into another table where the column is numeric format. If I don't filter out for the non-numbers, then the sql statement will crash. The string is unknown length so I am still stuck on this one.

    Any more ideas?

  • October 10, 2003 at 7:58 pm

    #477942

    quote:

    Thanks Jonathan.

    I actually never thought that 4d4 would evaluate to a number. For me, '4d4' evaluates to only 4. Totally bizarre. I wonder if it is a bug or hidden feature?

    My intention is to use a function that will determine whether a list of values I obtain from varchar column (don't ask why a number is stored in varchar field) is in numeric format because I need to append that number into another table where the column is numeric format. If I don't filter out for the non-numbers, then the sql statement will crash. The string is unknown length so I am still stuck on this one.

    Any more ideas?

    

    CASE WHEN vcCol LIKE '%[^0-9]%' THEN 0 ELSE 1 END

    --Jonathan



    --Jonathan

  • October 10, 2003 at 8:46 pm

    #477943

    very nice, thanks!

    Billy

Viewing 5 posts - 1 through 4 (of 4 total)

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