REPLACE and Unicode characters in SQL

Question

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?

Answer 1

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.