Post reply

IsNumeric not working

  • January 8, 2010 at 7:39 am

    #1100882

    Yes, I left out 0. Otherwise, I would use Like for the test.

    Here's how I'd be likely to write something like this:

    if object_id(N'tempdb..#T') is not null

    drop table #T;

    create table #T (

    ID int identity primary key,

    Val varchar(10));

    insert into #T (Val)

    select '1' union all

    select '-1' union all

    select '1-10' union all

    select 'Bob' union all

    select '1.5' union all

    select '1d10';

    select

    case

    when Val like '%[^0-9.|-]%' escape '|' then null

    when isnumeric(Val) = 1 then Val

    else null

    end as NumVal

    from #T;

    The first test returns a null if any character other than those listed is in the string. I'm not sure if SQL will short-cut this, but this gives it the opportunity to do so. In a 1,000-character string, using "not like" means it has to test every single character. Using "like" means it just has to test till it finds a single character that's not in the allowed set.

    - 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

  • January 11, 2010 at 9:56 am

    #1101919

    Thanks everyone for the responses. It fixed my issue.

Viewing 2 posts - 16 through 16 (of 16 total)

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