REPLACE and Unicode characters in SQL

sql, sql-server, unicode

Solution

Try this (assuming you have SQL server, where I could just reproduce that behavior):

Select Replace(N'ClΘmentine', N'Θ', N'é')

You must tell SQL Server that it deals with Unicode strings, that is what the N is for.

Problem

I have some data with messed-up accented characters. For example in the data we have things like `ClΘmentine` that should should read `Clémentine` I'd like to clean it up with a script, but when I do this for example ``` Select Replace('ClΘmentine', 'Θ', 'é') ``` this is what I get: `Clémenéine` Apparently Θ matches both Θ and t. Any ideas what's going on and what I can do to make this work?

Original source