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:

  • Hebrew
  • Arabic
  • English
  • French

3 Answers 3

  • Language support comes from datatype: use nvarchar/nchar (which stored unicode basically)
  • Collation is sorting + comparing + non-unicode code page

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:


    LatinCollationCol COLLATE Arabic_CI_AI_KS = N'عربي/عربى';
  • I was trying to create schema for language. below is the code create table #temp ( str Nvarchar(100) COLLATE Arabic_CI_AI_KS ) insert into #temp(str)values('عربي/عربى') select * from #temp drop table #temp BUT it is showing me question marks only...
    – Pankaj Garg
    Feb 16, 2012 at 9:12
  • You need N before literals to say "this is unicode". Like my example...
    – gbn
    Feb 16, 2012 at 9:20
  • Ya.Thanks. I just checked. But, I think no need to mention collate in order by clause as well as in schema. Just NVarchar type is enough for column.
    – Pankaj Garg
    Feb 16, 2012 at 9:23
  • This is updated one create table #temp ( str Nvarchar(100) ) insert into #temp(str)values(N'عربي/عربى') select * from #temp drop table #temp
    – Pankaj Garg
    Feb 16, 2012 at 9:25
  • @PankajGarg: if you want to sort the English/French/Hebrew way, or compare like these languages, then you will. That is, if the sort/compare doesn't match the column collation, you'll need a COLLATE clause
    – gbn
    Feb 16, 2012 at 9:36

@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?

  1. Yes, it is possible to accomplish both of these.
  2. No, it does not require Regular Expressions (as much as I am a huge advocate of them and SQLCLR in general).

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 function:

CREATE FUNCTION dbo.DetermineLanguage(@Snippet NVARCHAR(5))
   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
       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;

The results:

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

  • SQL Server doesn't support utf-8... it uses UCS-2
    – gbn
    Feb 16, 2012 at 9:05
  • mea culpa, I had forgot that fact...
    – niusha
    Feb 16, 2012 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.