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

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.