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:
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
ORDER BY LatinCollationCol COLLATE Arabic_CI_AI_KS = N'عربي/عربى';
@gbn has already explained using
NVARCHAR, that it holds UTF-16 characters (though default interpretation only handles the UCS-2 / Basic Multilingual Plane (BMP) code points), and COLLATE.
But there were two follow-up questions:
select statement to list the records in order of below. 1. Arabic, 2. Hebrew 3. English
can you tell any way to filter only Arabic records or only Hebrew?
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
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.
CREATE FUNCTION dbo.DetermineLanguage(@Snippet NVARCHAR(5)) RETURNS TABLE AS RETURN WITH cte AS ( SELECT UNICODE(LTRIM(@Snippet)) AS [CodePoint] ) SELECT CASE WHEN (cte.CodePoint BETWEEN 1536 AND 1771) OR (cte.CodePoint BETWEEN 1902 AND 1917) THEN 1 -- http://unicode-table.com/en/search/?q=arabic WHEN (cte.CodePoint BETWEEN 1425 AND 1524) OR (cte.CodePoint BETWEEN 64285 AND 64335) THEN 2 -- http://unicode-table.com/en/search/?q=hebrew WHEN (cte.CodePoint BETWEEN 65 AND 90) OR (cte.CodePoint BETWEEN 97 AND 122) THEN 3 -- http://asciicodes.com/ (English) ELSE 4 END AS [LanguageNumber] FROM cte;
The test case:
SELECT data.*, num.LanguageNumber FROM ( SELECT N'יִﬞ ' AS [SampleText], 'Hebrew' AS [Language] UNION ALL SELECT N'öû' AS [SampleText], 'Not English' AS [Language] UNION ALL SELECT N'نݶ' AS [SampleText], 'Arabic' AS [Language] UNION ALL SELECT N'what?' AS [SampleText], 'English' AS [Language] ) data CROSS APPLY dbo.DetermineLanguage(data.[SampleText]) num ORDER BY num.LanguageNumber;
SampleText Language LanguageNumber نݶ Arabic 1 יִﬞ Hebrew 2 what? English 3 öû Not English 4
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