CODEWITHSUNDEEP
sqldatabasesql-server-2008-r2
user3191666
user3191666

Reputation: 159

Archive old Information in table

I want to archive old information in my table.

If the value in T1C2 is a number (or less than 5 characters) then I want to replace it with the word archive and the value in T1C1.

 T1C1  T1C2 

  1     London
  2     New York
  3     2342
  4     Cardiff  
  5     2394
  6     Sydney
  7     2342
  8     2343
  9     7345

Result

  T1C1  T1C2   

  1     London
  2     New York
  3     Archive3
  4     Cardiff  
  5     Archive5
  6     Sydney
  7     Archive7
  8     Archive8
  9     Archive9

Upvotes: 0

Views: 39

Answers (2)

Deep
Deep

Reputation: 3202

Try this one :

UPDATE yourtable
SET    T1C2 = 'Archive' + Cast(T1C1 AS VARCHAR(100))
WHERE  isnumeric(T1C2) = 1
        OR Len(T1C2) < 5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can do this with an update:

update mytable
    set t1c2 = 'Archive' + cast(t1c1 as varchar(255))
    where t1c2 not like '%[^0-9]%';

You could also use isnumeric() for the where, but I you need to check for other conditions:

where isnumeric(t1c2) = 1 and t1c2 not like '%[.e]%'

Upvotes: 1

Related Questions