SQL Server 2005 Convert Ascii to Unicode (UTF-8 -> nvarchar)

I have data in an nvarchar field with data in ascii format: "Zard Frères Guesta"

How do I convert it to a readable(unicode) format in t-sql开发者_JS百科?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62406 has a function that will do this:

select dbo.utf8_to_utf16('Zard Frères Guesta')

Results:

Zard Frères Guesta

I think what you are saying is that you have what should be the two bytes of a single unicode character in two consecutive unicode characters (the high bytes are probably all 0 bytes). This can happen for all sorts of reasons if you aren't handling the code pages correctly during insertions.

You'll need to get these back to single bytes and then re-encode the data.

I think your problem is that you're using CODEPAGE = 1252 (a single-byte characterset) when you're loading the data in OPENROWSET.

Try using 1202 (UTF-16), or possibly 1208 (UTF-8)

Cheers