Take the 2-minute tour ×
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's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Feb 16 '12 at 14:23

This question came from our site for professional and enthusiast programmers.

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:

Example

ORDER BY
    LatinCollationCol COLLATE Arabic_CI_AI_KS = N'عربي/عربى';
share|improve this answer
    
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 '12 at 9:12
    
You need N before literals to say "this is unicode". Like my example... –  gbn Feb 16 '12 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 '12 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 '12 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 '12 at 9:36

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

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

@gbn has already explained using NVARCHAR, that it holds UCS-2 characters, 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

and:

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))
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;

The results:

SampleText  Language     LanguageNumber
نݶ          Arabic       1
יִﬞ          Hebrew       2
what?       English      3
öû          Not English  4
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.