Special characters globally changes in SQL server

I wrote a SQL query in this blog to replace special characters globally. Now , I am going to modify this query with another ASCII UTF-8 table concept.

Look ASCII UTF-8 table and find the character whose description is 'Acute accent, spacing acute', it's not apostrophe.

If you have this character in your database table data, it doesn't allow you to update your table. It can be replaced manually but if you have this character in many places you should have query to replace those bad characters.

Let's look that table, you can see the Raw Encoding for that bad character is:

0xB4

which is in hexdecimal notation.

let's convert to base 10 value:

B means 11=>16*11=176

B4=>176+4=180

so,

180 = ' (x b4)

147, 148 = " (x 93, 94)

Hence the queries:

//replace by apostrophe
UPDATE table
SET ColumnName= replace(ColumnName, CHAR(180), '''')

UPDATE table
SET ColumnName= replace(ColumnName, CHAR(148), '"');

UPDATE table
SET ColumnName= replace(ColumnName, CHAR(147), '"');

Happy Coding !!!