SQL Server, varchar data to nvarchar data

Question

I've got a database with collation Danish_Norwegian_CS_AS and lots of varchar columns. I'd like to convert all this data to unicode, but haven't found a way to convert this data yet. If I've understood correctly, the encoding used is UCS-2 little endian.

For example I've got a column containing 'PÃ¥l-Trygve' which is easily converted with C# to 'Pål-Trygve' using Encoding.Default.GetString(Encoding.UTF8.GetBytes("PÃ¥l-Trygve"));

Is there a way to do this conversion in the Microsoft SQL Server client?

Answer 1

Could you just use the CONVERT statement - or am I missing a nuance here?

From http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 2

In sql server if you put a N character before your favorite statement, it convert to unicode. for example:

update mytable
set mycol = N'PÃ¥l-Trygve'

or

insert into mytable
values (N'PÃ¥l-Trygve',...)

Answer 3

In your comment to Sam's answer you said "data in the varchar column is the binary code for an ucs2 little endian string and not ascii.". Which differs from what said in the question, where you have text.

So, I'd create a whole set of parallel tables (with nvarchar columns) and load them via some c# code. Then drop, rename etc. This isn't something to do easily in t-sql