CODEWITHSUNDEEP
sqlt-sql
MISNole
MISNole

Reputation: 1062

Updating Records while leaving part of the record

I'm trying to update two tables containing account names /building names after Winn-Dixie's purchase of Sweetbay. The account table has two columns I'd like to update: name and internalname. The name column should be straight forward as I'd update the records where anything is like 'Sweetbay%' but the internalname is trickier it contains names like "Sweetbay Supermarket - New Tampa' or 'Sweetbay Supermarket - Palmetto'. In these cases, I'd like to keep the text after the "-" so it becomes "Winn-Dixie - New Tampa" or "Winn-Dixie - Palmetto".

What would be the best way to handle this? Any suggestions?

Also, I'd like to began clean up on the naming convention of "Winn-Dixie" as well because there are instances of the account "Winn Dixie" that should include the "-" to standardize the convention. I believe this would be done the same way as above. Thoughts?

Upvotes: 0

Views: 38

Answers (1)

adaskos
adaskos

Reputation: 151

If the cases you mention are all there is, then try some string manipulation (see REPLACE and other string functions at MSDN)

E.g.

UPDATE #Table 
SET name = 'Winn-Dixie -' + RIGHT(Name, LEN(NAME) - CHARINDEX('-',Name))
WHERE name LIKE 'Sweetbay%'

But in real life scenarios you cannot go through all the records, identify each change necessary and code around it. There are tools specifically targeted to that. Try looking up data cleansing.

I remember a presentation of MS's Data Quality Projects (DQS). It seemed quite promising, but I'm sure there are other tools out there too.

In the end human interaction is unavoidable.

Upvotes: 1

Related Questions