I've got a description column in SQL Server that contains a single quote that can't be stripped out. I'm guessing it's from the extended character set but I'm not sure how to strip it.
I've tried
Replace([Description], CHAR(39), '')
Replace([Description], '''', '')
Neither of these work but do remove other single quotes in the same field which is why I think someone's entered something from the extended set.
You may be dealing with Microsoft Word's "magic quotes", as with this article. If your description field is of type NVARCHAR and not VARCHAR, that might be the reason that the database is even able to store these characters in the first place.
You might really put some thought into fixing the user interfaces for the application to strip or replace such characters. But since you're at where you're at, you might take a cue from the article I referenced. The right-quote character is NOT ASCII char(39). It is Unicode character 0x2019 (hexadecimal) or 8217 (decimal). So you need to replace NCHAR(8217), not CHAR(39).
Rather than doing this inline in a SQL statement, you might want to write a function to do this to all offending characters in a NVARCHAR parameter passed into the function. Then, call the function in your SQL statements to update the date.