Collation

Question

Collation

Saru-245170

SSC Enthusiast

Points: 172
More actions
July 12, 2005 at 1:24 am

#363111

Hi,
We are planning to store multi language (mostly asian language Chinese, Korean, Thai & English) data in a single database. All the necessary character fields are set to nchar or nvarchar. No issues in storing or retrieving
I am facing problem in sorting order. I am using the default collation.
What collation do I need to use? I cannot change the collation for the entire SQL Server (many other databases are there). Many of my stored procedures and functions are using temp table so i used COLLATE database_default wherever nchar/nvarchar is used.
Reply,
rgds,
Aru.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

Run a search for collation in the BOL(books online) and you will get code to do per column collation for your langauges. I would also save the stored procs T-SQL as Unicode before executing it. Try the link below for a localization tool kit. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=6b6fb09f-f25c-48e9-9e26-b55144600da1&DisplayLang=en

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Carl B. SSCertifiable Points: 7931 More actions · Answer 2

Hello Arumugan,

What is your problem(s) exactly?

You could use the COLLATE keyword in the ORDER BY clause.

You could also (i don't know if it is your need) add a column stating which langage is used to store the values for this row and use it in the WHERE clause to restrict thet data you want to retreive and sort.

Best regards,

Carl

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 3

Aru,

We have a similar situation here. Our solution was to apply SP4 to SQL 2000 and use the SQL_Latin1_General_CP850_BIN2 collation setting on the database. We discovered some issues using other collations with different languages (Czech was one of them). The drawback is your database is now Case Sensitive, but for us that was a "benefit"

good luck,

Darrell

Saru-245170 SSC Enthusiast Points: 172 More actions · Answer 4

Hi,

My requirement is based on user selected culture, datagrid (ASP.NET) need to be sorted. If Chinese (PRC) is selected, datagird need to be sorted based on stroke.

My problem is those chinese characters are not properly sorted.

I am using default collation. What need to be done at UI/DB level. I am using sp to fetch recors.

Thanks & rgds,

Aru.

Perry-300990 Default port Points: 1401 More actions · Answer 5

I think the default SQL Server 2000 collation will collate CJK characters based on Unicode codepoint, and that is not a stroke order collation.

So you'll want to collate in the grid, or anyway, at some point along the way.