Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Tampa, FL (USA)
    Posts
    9

    Unanswered: Change field with numeric and alphanumeric to numeric

    I have a text field with records that mostly contain numbers, but in some of the records the field is alphanumeric, such as P234032. I want to convert the field to numeric, and just allow any alphanumeric fields to be ignored, and become 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

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Does this help

    Code:
    set nocount on
    go
    create table #test (col1 char(10), col2 char(10))
    go
    insert #test values ('BU89090', 'record 1' )
    insert #test values ('GHJ9213', 'record 2' )
    insert #test values ('79898', 'record 3' )
    insert #test values ('89067', 'record 4' )
    insert #test values ('09889067', 'record 5' )
    go
    
    select *
    from #test
    go
    select convert(int, case when isnumeric(col1) = 1 then col1 else NULL end) as intCol1, col2
    from #test
    go
    drop table #test
    Output
    Code:
    col1       col2       
    ---------- ---------- 
    BU89090    record 1  
    GHJ9213    record 2  
    79898      record 3  
    89067      record 4  
    09889067   record 5  
    
    intCol1     col2       
    ----------- ---------- 
    NULL        record 1  
    NULL        record 2  
    79898       record 3  
    89067       record 4  
    9889067     record 5
    MCDBA

  3. #3
    Join Date
    Jul 2003
    Location
    Tampa, FL (USA)
    Posts
    9
    achorozy,

    Thanks for the reply! I haven't tried this yet, but will have a chance first thing tomorrow morning, and will let you know. The SQL Server 2000 course that I just completed was pretty thorough, but seems like it didn't cover the nitty gritty things that I need to know right now.

    This is sure a different animal than MS Access!

    Thanks again,

    Randy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •