Switch column data where a column contains a number?

Question

I have a table that have 3 columns

id, company and adress

i found a bug today that saved the adress in the company-column and company in the adress-column SOMETIMES, i have corrected the bug and now im trying to put the data in the right places

every adress has a number in it so my guess is that the easiest way is to switch adress and company columns if there is a number in the company-column (if there should be a number in the real company name this wont matter that much :p).

How should i write this in TSQL?

Answer 1

I'm not sure this is right thing to do here but as I can't think of any other alternative this should do it.

Update dbo.MyTable
Set Company = Address,
    Address = Company
Where Company like '%[0-9]%'

Answer 2

You can try this: i put a simple protection to avoid the swap if the company adress already contains a number

insert into COMPANY (NAME, ADDRESS) 
VALUES ('2 bld d''Italie' , 'CA') , 
('Take 2' , 'anselmo street 234') , 
('Microsoft' , '1 Microsoft Way Redmond'), 
('lake street 14' , 'Norton'), 
('lake street 17' , 'trendMicro');

SELECT * FROM COMPANY

UPDATE COMPANY set NAME = ADDRESS, ADDRESS = NAME
WHERE NAME like '%[0-9]%' and (ADDRESS not like '%[0-9]%')

SELECT * FROM COMPANY

You could notice that the take 2 line won't be swapped