isnumeric not working

  • isnumeric not working.........

    select isnumeric('26d5')

    it should result in = 0

    but it is showing me 1...any help?

    why is it not working??

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • "d5" means "decimal 5", similar to scientific notation. It means "26 followed by 5 zeroes". That's a number.

    It's one of the main weaknesses of IsNumeric.

    Sometimes, you have to use something like this:

    declare @STR varchar(100);

    select @STR = '26d5';

    select

    case patindex('%[^0-9]%', @STR)

    when 0 then cast(@Str as Int)

    else null

    end;

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Heh... I normally hate the use of semantics but I have to deploy them for this one. Please forgive me... :blush:

    Despite appearances, ISNUMERIC is functioning properly even when it allows something as odd as your '26d5' example ... It works precisely the way it was designed to work and for exactly the task it was designed to solve. It identifies things that could be changed to some type of numeric data type. It was never desired for nor intended to be used as an ISALLDIGITS function. ISNUMERIC will even take currency symbols, commas, decimal points, engineering and decimal notations (like Gus pointed out), tabs, certain spaces, carriage returns, line feeds, and a whole bunch of other things.

    Gus shows one possible way to derive functionality similar to an ISALLDIGITS function. You can also use [font="Courier New"]WHERE somecolumnname NOT LIKE '%[^0-9]%'[/font] to check a whole column. For more information on the nuances of ISNUMERIC, please see the following article... heh... I can personally vouch for the author. 😛

    http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1073&lngWId=5

    --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)

Viewing 3 posts - 1 through 2 (of 2 total)

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