March 9, 2017 at 5:20 am
hi everyone
i was testing collation with japanese characters and when i inserted the data into the database using collation SQL_Latin1_General_CP1_CI_AS the japanese letters were displaying but when i created a new column of type VARCHAR and inserted the data without using N' date' i am getting ??????
CREATE TABLE [dbo].[Customers](
[CustomerOrders] [int] NULL,
[CustomerValue] [int] NULL,
[CustomerID] [int] NULL,
[Language] [nchar](10) NULL,
) ON [PRIMARY]
GO
i insert using :
insert into customers values
(39, 378,7,N'スタンダードチャ');
and i get
CustomerOrders CustomerValue CustomerID Language
1 1 1 NULL
2 1 2 NULL
39 378 7 スタンダードチャ
Now i insert using no (N'') and i get
insert into customers values
(39, 378,87,'スタンダードチャ');
the result:
CustomerOrders CustomerValue CustomerID Language
39 378 87 ????????
Now i change the collation of the column to Japanese
ALTER TABLE [Customers]
ALTER COLUMN language nvarchar(400) COLLATE Japanese_CI_AI_KS_WS NULL;
when i query the table again the result is the same:
CustomerOrders CustomerValue CustomerID Language
39 378 87 ????????
Doesnt the data change with the collation? or do i have to update the column value?
Do i lose my data?
thanks
KY
March 9, 2017 at 7:48 am
I think you are confusing the issue here. When you create a string literal without the N it is a varchar in the ASCII character set. To get your value into the UNICODE set you need to use the N. This can be a bit confusing until you understand what is happening. Those Japanese characters will have to go into an nvarchar datatype at all times. Changing the collation is not going to change the data in the table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 9, 2017 at 9:40 am
Ok say that I did have it inserted as N'Japanese characters' and I do a select and question marks are returned. Then how do I make the data readable without losing my data as in dropping my column. Will changing the collation of the column resolve this? An example would be helpful. Thanks
Kal
March 9, 2017 at 9:47 am
hurricaneDBA - Thursday, March 9, 2017 9:40 AMOk say that I did have it inserted as N'Japanese characters' and I do a select and question marks are returned. Then how do I make the data readable without losing my data as in dropping my column. Will changing the collation of the column resolve this? An example would be helpful. Thanks Kal
You don't. If you insert unicode values without the N to indicate the string literal is nvarchar the value is lost. It tried to stick characters into a datatype that can't understand them. If you are seeing the question marks returned it is because the values were inserted incorrectly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply