Search code examples
sqlsql-servercollate

Loss of Unicode data - due to collation change?

I had a table with a collation of Thai_CI_AS which did not store special characters like à properly. When storing à it gets rendered as a question mark: ?

I have solved the problem by changing the collation of that table to default which is latin*.

New data (special characters) are now being stored correctly.

But the old data still displays ???.

How do I refresh or whatever it's called to make the old data display properly?

----- edit -------

Is there an sql query that allows one to change all ? to something else like à?

Example

 ?sometext?
 ??sometext??
 ?????
 sometext??

and turn all to

 àsometextà
 àsometextà
 ààààà
 sometextàà

Thank you, The following columns are the one that contains those kinds of data's,

 id_name1, id_name2, id_name3.

Please help. Thank you very much.

Solution

  • I'm sorry to say, but your old data is gone. I don't think this is because of the collation; it probably happened because you did this:

    INSERT dbo.table(column) SELECT 'à';

    Instead of properly prefixing Unicode data with the N character:

    INSERT dbo.table(column) SELECT N'à';
---- this is important ---------^

    But again, there is no getting that old data back... you even said yourself that when you stored the data under Thai collation, it showed ? - this means the data was never stored properly in the first place (most likely because of the missing N).