Database to support multilingual data

Question

Post reply

Database to support multilingual data

Sathiya priya

Mr or Mrs. 500

Points: 560
More actions
February 26, 2008 at 6:20 am

#154150

Hi ,
My database has to support multlingual data.since all the application
Business Logic lies in the database Stored Procedure and functions,
I can not change the data type to nchar or nvarchar.
Is there any way to support multilingual data with out changing the data type....
Or by changing collation type will support multilingual data..if it so what is the collation?????
Thanks & Regards,
Priya

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

To the best of my knowledge this can't be done so. You can have only a particular collation associated with an object.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Sathiya priya Mr or Mrs. 500 Points: 560 More actions · Answer 2

Thanks for Your reply. I also get the same reply from other forums 🙁

Regs,

priya

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

You get the same answer. What exact is your need if you can tell me i can think of other possible solutions if i have any.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Kimberly.Hahn SSC-Addicted Points: 470 More actions · Answer 4

You cannot store multi-byte data in single byte string data types. But you can store data for multiple languages that only require single byte data in one column. It's not something that is pretty or fast to deal with, but it can be done.

This can be done if the assumption is made that a given user only wants to view data in one language. It requires splitting data into two tables; the second table stores multiple instances of the data for a given record, each instance in a different language (MyTable having a column for Identity and any date or numeric type data; MyTableStringData having a column that foreign keys to MyTable, a collation indicator, an indicator for which "column" the user wanted the data in, and a field to store the actual data). The thing you need to be able to do is determine what language (and thereby collation) the data goes with. The column that contains the actual data can then be retrieved for a given language/collation using the COLLATE clause to cast it into the correct collation for the current retrieval need. With this approach you do have to do extra work to pivot the results from rows of "column name", "row data" into column & row to allow application interface code to be oblivious to the data storage method being used by the DB.

Sathiya priya Mr or Mrs. 500 Points: 560 More actions · Answer 5

thanks for the reply....

My requirement is like this ....

User is entering his profile information example Name in Khmer (Cambodian language), and his education details in English.

We have data base which has the data type as varchar for these fields.

All i want to know is with out changing the data type to nvarchar can we store these languages.

I read in some article if you change it to nvarchar also..You need to specify proper collation to support these data.

Our application has to support almost all the languages. English as default and their regional languages.

vidya_pande SSCertifiable Points: 5275 More actions · Answer 6

You can specify collation i.e. language specification to varchar and char columns.

You can not/need not specify collation/ language specification to the nvarchar/nchar columns; because it [nvarchar/nchar i.e. unicode] can store data of all supported languages.

vidya_pande SSCertifiable Points: 5275 More actions · Answer 7

and yes you can store english and combodian data in varchar column nvarchar is not required. But you need to specify combodian collation at column level/ DB level and if required Server level.

tatiana.vasilevskaya SSC Journeyman Points: 82 More actions · Answer 8

Hi,

I came to similar issue while supporting German database from Australia. We have repeating town information in German and in English: like one row in English = "Grassing" and the same town spelled in German looks like "Graßing"

Statement as following:

select * from MyTable where [NAME] like 'Grassing%'

gives me 2 rows, as SQL server recognizes them as "the same":

50464Grassing

50464Graßing

Primary key that suppose to incorporate those 3 rows fail to be created...

Set up Binary collation for whole database or for table or for one column is working but is automatically cause case sensitivity, therefore programmers should take care about that. In most cases this is not acceptable.

Set up any collation with 850 paging is OK, but data retrieved look wierd - for example instead of "ß" it returns "-". This is not good as well...

So - we replaced primary key with index and leave search on a hands of application developers. Not really nice solution, but looks like there is nothing better.

Would be very much interested in any ideas around this issue.