How to Remove Some Thing from a Column’s Data

Here was a problem I was faced with today. A friend of mine was experiencing issues with two databases not syncing properly because one had a column (City) that contained a special character (a single quote). We needed to craft a query that would remove the single quote without changing any other portion of the column's data. There were two considerations that needed to be made:

1. Ensuring the single quote was properly escaped so that it did not cause an error at run time.
2. How to properly place the REPLACE function inside the UPDATE command.

It took me about 30 minutes to figure it out but here is what I came up with.

UPDATE [ARCUSTOMERS] SET City = REPLACE(City, '''', '') FROM [ARCUSTOMERS] WHERE City LIKE '%''%'