Tuesday, February 19, 2013 5:11 AM
We have Sql Server Express 2012 installed.
Our aim is to have languange specific named instance, so that when data for any other languange(other than the collation of named instance) is entered it should be stored and ?????.
To achieve this we have installed the named instance with the collation for (e.g)Chinese language .
In this named instance, for testing, we have created database and table(with the datatype varchar).
We have written the insert statement for this table in both English and Chinese languange.
The data for the chinese language is saved as chinese character, but the data in English language is also saved as english character. Our requirement is that if the data for any other languange(other than the collation,for e.g. Chinese, of named instance) is entered it should be stored and ?????.
Any suggestion, how this can be achieved ?
Tuesday, February 19, 2013 5:30 AM
use the column data type nvarchar instead of varchar.
Tuesday, February 19, 2013 6:00 AM
I think you are confused with the term 'Collation'. Actually collation referes to certain rules of a particular language (say for example Chineese) that corresponds to the sort order and character comparisons and also the case and accent sensitivity. A code page is associated with each collation that controls the sort and comparison properties. A collation doesn't prevent other language characters from storing in the column.
So when you define a VARCHAR column with a Chineese collation that doen't prevent English characters to be stored in that column (or anyother charcters).
Instead of using CAHR or VARCHAR with a collation, you can also use Uniocode columns using NCHAR, or NVARCHAR a below, but keep in mind that you cannot prevent other characters from the column:
CREATE TABLE Translate ( ColNonUnicode VARCHAR(100) COLLATE Chinese_PRC_CI_AS, ColUnicode NVARCHAR (100) ) GO INSERT INTO Translate VALUES ('Google', 'Google') INSERT INTO Translate VALUES (N'谷歌', N'谷歌') SELECT * FROM Translate /* ColNonUnicode ColUnicode ------------- ------------- Google Google 谷歌 谷歌 */ DROP TABLE Translate
- Proposed As Answer by scott_morris-ga Tuesday, February 19, 2013 1:52 PM
Tuesday, February 19, 2013 7:41 AMThanks for your reply.
Any way to achieve my requirements.
Any way to prevent other language data to be saved. Only language specific data needs to be saved.
Tuesday, February 19, 2013 2:04 PM
I think you'll have to write your own code to prevent this - at least I can't think of any way of doing it "out of the box".
The problem will be to determine which language the text is written in and I don't know any good way to do this. You might have to look at each character in a string and then based on the characters, do your own determination of the language. If the options is only Chinese and English it might be possible, but the problem would be if have string in different languages that doesn't contains any langauge specific characters. E.g. a string like "The quick brown fox jumps over the lazy dog". The characters in this string exists in many languages, so how would you determine if it's English, German, French, Danish etc. just by looking the at characters.
Steen Schlüter Persson (DK)
- Marked As Answer by Iric WenModerator Thursday, February 28, 2013 8:40 AM