SQL Server and Arabic language

I have read some articles written about how this products Office 2010 by default support not only Hijri Date but also UmAlQura Date and this good news.

This article show you some points related to Arabic language (which is my mother tongue) with Sql Server.

In general Sql server deals with any language in the same way except some issues become different from language to other language.
So let's begin by Collation and see how Sql Server stored data and retrieve data in Arabic language.

What is a Collation?

A collation determines the rules SQL Server uses to compare and sort character data and determines which characters can be stored in non-Unicode character data types. Collation can be specified at the instance level, database, column level or clause level.

So let's begin by this example to make the points clear:

CREATE TABLE [dbo].[Test](
--here stored data as ASCII char
    [Name_Char_Latain] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--here stored data as Unicode
    [Name_nChar_Latain] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--here stored data as ASCII char
    [Name_Char_Arabic] [char](10) COLLATE Arabic_CI_AI NULL,
--here stored data as Unicode
    [Name_nChar_Arabic] [nchar](10) COLLATE Arabic_CI_AI NULL
) ON [PRIMARY]

GO

If your database collation is SQL_Latin1_General_CP1_CI_AS and you tried to insert Arabic data you will get ??? Characters so there are two solutions for these problems: Change database collation or use N' '

You can change database collation as following:

USE [master]
GO
ALTER DATABASE [TestDb] COLLATE Arabic_100_CI_AI
GO

Or you can write your statement like this

INSERT dbo.test VALUES (N'بدر',N'بدر',N'بدر',N'بدر');

After we created the table then inserts these records

INSERT dbo.test VALUES ('بدر','بدر','بدر','بدر');
INSERT dbo.test VALUES ('احمد','احمد','احمد','احمد');
INSERT dbo.test VALUES ('أحمد','أحمد','أحمد','أحمد');
INSERT dbo.test VALUES ('أيات','أيات','أيات','أيات');

Then run these commands:

USE TestDb
GO

SELECT * FROM dbo.test ORDER BY Name_Char_Latain
SELECT * FROM dbo.test ORDER BY Name_nChar_Latain
SELECT * FROM dbo.test ORDER BY Name_Char_Arabic
SELECT * FROM dbo.test ORDER BY Name_nChar_Arab

And you will get result set as the below:

Now what about the hijri date and UmAlQura date?

By default Sql server support Hijri date format and you can do it by using CONVERT function so let's see these examples:

SELECT CONVERT(char(40),GETDATE(),130)
SELECT CONVERT(char(40),GETDATE(),131)

The result set:


29 ربيع الاول 1431 12:53:11:130AM

29/03/1431 12:53:11:130AM

So Sql Server does a great effort to display the date as Hijri but what about the UmAlQura date.

You need to write custom code using .NET CLR to implement it in SQL Server and this great feature introduced with version of SQL Server 2005 to enable you to write .Net code and by this approach we see new functions and data types like HIERARCHYID.

Let's see how we can Convert Gregorian Date to UmAlQura Date by using .Net UDF:

1. First open VS 2005/2008/2010 and create CLR database project

2. Choose a database connection for your reference

3. Right click on your project and Add New item.. and select UDF

4. Add reference using System.Globalization;

5. The complete code:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString UmAlQuraConverter(DateTime date, string format)
    {
        return new SqlString(getUmAlQuraDate(date, format));
    }

    static string getUmAlQuraDate(DateTime date, string format)
    {
        UmAlQuraCalendar umAlQuraCal = new UmAlQuraCalendar();
        CultureInfo umAlQuraCulture = new CultureInfo("ar-SA");
        umAlQuraCulture.DateTimeFormat.Calendar = umAlQuraCal;
        return date.ToString(format, umAlQuraCulture);
    }
};

Then choose deploy solution

Then run this command to enable using CLR in your SQL server instance

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Then run this command to get UmAlQura Date

SELECT dbo.UmAlQuraConverter(GETDATE(),'dd/MM/yyyy')

And you will get result as UmAlQura Date.


Note:
If you use Sql server database as backend system and .net application as frontend system ,in this case leave your date column as it's (Gregorian date) and just change the formatting at design level or user interface application by using inline function to format the date or use Global file to change it at the application level so by this approach you decrease the operations of convert date at Sql server and you avoid to have multiple columns or create custom data type or use custom functions and this make the integration easy to you because your data stored as standard or default data types.