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 Member Posts: 2
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 Member Posts: 30
    [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.