Convert char columns to nvarchar, in order to chan

问题:

I have a table that was imported from another source as a column with the char datatype, but the field has international characters. I would like to use nvarcvhar in my table. How can I achieve this?

I already tried updating the columns (using an alter table statement, or "cast as") but the info stored did not get converted.

Thanks

回答1:

Your data is currently in EUC-CN, masquerading as CP1252. It is not lost.

You have several approaches available for conversion to Unicode (look at Converting SQL databases section for overview). In case of SQL Server, you can create extended stored procedures for conversion from EUC-CN to Unicode. This will work but it is not exactly easy (in this case use code page 51936 for your data).

With some luck, depending on what particular characters occur in your data, and what language packs you have installed, you might be able to convert as if from code page 936 like this:

ALTER DATABASE mydatabasename COLLATE Chinese_PRC

If this succeeds, do the same for every column you are going to convert:

ALTER TABLE mytablename ALTER COLUMN mycolumnname
        varchar(4000) COLLATE Chinese_PRC NOT NULL

And only then convert them to NVARCHAR.

回答2:

like this

ALTER TABLE TableName ALTER COLUMN ColumnName NVARCHAR(size)

example

CREATE TABLE test (bla VARCHAR(50))
GO
ALTER TABLE test ALTER COLUMN bla NVARCHAR(50)

Make sure that you prefix the string with N when doing the insert

INSERT test VALUES (N'漢語')

SELECT * FROM test

output

bla
--------------------------------------------------
漢語

(1 row(s) affected)

Whatever you do, don't use the SSMS designer/wizard, it will recreate the whole table, see here for example When changing column data types use ALTER TABLE TableName ALTER Column syntax, don't drop and recreate column

回答3:

alter table your_table alter column your_column nvarchar(length)

SQLFiddle example