Convert numbers in text field to numeric, eliminate text - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Convert numbers in text field to numeric, eliminate text

ghh3rdghh3rd Posts: 2Member
I have a text field with records that mostly contain numbers, but some are alphanumeric, such as P234032. I want to convert the field to numeric, and just allow those fields with alphanumeric strings to be ignored, and be null.

I'm used to doing this in Microsoft Access - it just ignores those strings and they become null - how can I do this in SQL Server 2000?

Thanks,

Randy

Comments

  • DaedaliusDaedalius Posts: 30Member
    [code]
    create table t1 (c1 varchar(32));
    create table t2 (c1 int);

    insert into t1 values ('123123');
    insert into t1 values ('898A98');
    insert into t1 values ('G123');
    insert into t1 values ('999999999');

    insert into t2
    select case isnumeric(c1)
    when 1 then c1
    else null
    end
    from t1;

    select * from t1;
    select * from t2;
    [/code]


    : I have a text field with records that mostly contain numbers, but some are alphanumeric, such as P234032. I want to convert the field to numeric, and just allow those fields with alphanumeric strings to be ignored, and be null.
    :
    : I'm used to doing this in Microsoft Access - it just ignores those strings and they become null - how can I do this in SQL Server 2000?
    :
    : Thanks,
    :
    : Randy
    :

Sign In or Register to comment.