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:
- Ensuring the single quote was properly escaped so that it did not cause an error at run time.
- How to properly place the REPLACE function inside the UPDATE command.
SET City = REPLACE(City, '''', '')
WHERE City LIKE '%''%'