I have new SQL Server database which need to have support in 3 different languages.
What character sets and collations do I need to choose if I want my database to support the following:
migrated from stackoverflow.com Feb 16 '12 at 14:23
This question came from our site for professional and enthusiast programmers.
So, one column can store all languages, but it can have only one collation. You can "add" extra collations using computed columns, or coerce it during a sort/compare using the COLLATE clause:
You should use UTF8
On this link you will see hebrew, arabic, and french language encoded with UTF8 : http://www.unics.uni-hannover.de/nhtcapri/multilingual1.html
@gbn has already explained using NVARCHAR, that it holds UCS-2 characters, and COLLATE.
But there were two follow-up questions:
This won't work for all languages since many of them share character sets, but the following will work for the languages specified in these two questions since they, fortunately, each have characters sets that are unique from each other.
Just create an inline TVF like the following and it can be used in both WHERE and ORDER BY clauses. It accepts an NVARCHAR(5) which silently truncates the rest as there is no reason to take in the entire string when we just want to test the first character anyway. I make it 5 instead of 1 because there might be some leading spaces, hence the LTRIM() in the function.
Please keep in mind it does not sort within each group as that would require a different collation per language. That should still be possible, but requires starting with this piece to identify the different languages, and then break each language out to a computed column for a particular language defined with its appropriate collation.
The test case: