1

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.

nambla
  • 71
  • 2
  • 11

2 Answers2

2

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).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I see. Thanks though. Any advice, I want to just change all `?` to something else? Thank you. – nambla Jul 25 '12 at 19:08
  • Like instead of question marks make all of the question marks on all columns to an à? Example some column contains `?sometext?` some has `????` I want to change this to `àsometextà` and `àààà`. – nambla Jul 25 '12 at 19:09
  • @user1543644 do you think every question mark in your table was supposed to be a `à`, and no other "special characters" could have been used by anybody? What about real question marks at the end of a sentence? – Aaron Bertrand Jul 25 '12 at 19:11
  • There is no problem for that :) I know its all `à`. How can this be done? – nambla Jul 25 '12 at 19:13
  • does changing varchar to NVARCHAR do anything? – nambla Jul 25 '12 at 19:13
  • If you want to replace text, you use the [`REPLACE`](http://msdn.microsoft.com/en-us/library/ms186862(v=sql.100).aspx) function. Mind-boggling, I know. Changing the data type after the fact is not going to resurrect your old data. But are you saying the column is varchar, not nvarchar? Is `à` really the character you're after? If so, `UPDATE dbo.table SET column = REPLACE(column, 'à', '?');` – Aaron Bertrand Jul 25 '12 at 19:15
  • @user1543644 - `NVARCHAR` will also support those chars which are not in your keyboard. –  Jul 25 '12 at 19:17
  • I believe it should be... `UPDATE dbo.table SET column = REPLACE(column, '?', 'à');` thanks alot :) – nambla Jul 25 '12 at 19:27
  • @user1543644 yep, got it backwards, sorry. – Aaron Bertrand Jul 25 '12 at 19:28
0

If this question relates to your previous question, then the answer is Yes.

A VARCHAR column only holds single-byte characters. In your case, you should change the VARCHAR columns to NVARCHAR, and then change the columns' collation.

Community
  • 1
  • 1
devio
  • 36,858
  • 7
  • 80
  • 143
  • Hello, Thank you for your answer but everything is still ???? after doing what you said. But I guess I'll just manually change all the `?` after all :) – nambla Jul 25 '12 at 19:17