How to change string data with a mass update?

Question

I have a table (T1) in t-sql with a column (C1) that contains almost 30,000 rows of data. Each column contains values like MSA123, MSA245, MSA299, etc. I need to run an update script so the MSA part of the string changes to CMA. How can I do this?

Answer 1

You can use REPLACE function to do it.

Answer 2

update t1
set c1 = replace(c1,"MSA","CMA")
where c1 like "MSA%"

Answer 3

In addition to what fallen888 posted, if there are other values in that table/column as well you can use the LIKE operator in the where clause to make sure you only update the records you care about:

... WHERE [C1] LIKE 'MSA[0-9][0-9][0-9]'

Answer 4

I don't have SQL Server in front of me but I believe this will work:

UPDATE T1 SET C1 = REPLACE(C1, 'MSA', 'CMA');

Answer 5

While replace will appear to work, what happens when you need to replace M with C for MSA but not for MCA? Or if you have MSAD as well as MSA in the data right now and you didn't want that changed (or CMSA). Do you even know for sure if you have any data being replaced that you didn't want replaced?

The proper answer is never to store data that way. First rule of database design is to only store one piece of information per field. You should have a related table instead. It will be easier to maintain over time.

Answer 6

I have to disagree with HLGEM's post. While is is true that the first normal form talks about atomocity in E.F. Codd's original vision (is is the most controversial aspect of 1NF IMHO) the original request does not necessarily mean that there are no related tables or that the value is not atomic.

MSA123 may be the natural key of the object in question and the company may have simply decided to rename their product line. It is correct to say that if an artificial ID was used then even updates to the natural key would not require as many rows to be updated but if that is what you are implying then I would argue that artificial keys are definitely not the first rule of database design. They have their advantages but they also have many disadvantages which I won't go into here but a little googling would turn up quite a bit of controversy on whether or not to use artificial primary keys.

As for the original request, others have already nailed it, REPLACE is the way to go.