Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Unanswered: Adding and populating a column to existing table

    Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.

    Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.

    Any ideas?

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by garethleeds
    ...If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.
    Are you sure, or you just made mistake and both descriptions should be in Column B? I assume so, otherwise you would rewrite your numbers in colA
    Code:
    create table t(colA varchar(30))
    go
    
    insert into t values('000012')
    insert into t values('000023')
    insert into t values('123445')
    go
    
    alter table t add colB varchar(30)
    go
    
    update t 
    set colB = case when colA like '00001%' then
    		'description A'
    	   when colA like '00002%' then
    		'description B'
    	   end
    go
    note: when you create your table and colA is integer you won't be able found out how many zeros are in the begining as zeros at the begining are ignored

  3. #3
    Join Date
    Dec 2005
    Posts
    5
    Thanks very much that's really helpful!

Posting Permissions

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