This article show you some points related to Arabic language 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]

--Stored data as ASCII char

[Name_Char_Latain] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

--Stored data as Unicode

[Name_nChar_Latain] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

--Stored data as ASCII char

[Name_Char_Arabic] [char](10) COLLATE Arabic_CI_AI NULL,

--Stored data as Unicode

[Name_nChar_Arabic] [nchar](10) COLLATE Arabic_CI_AI NULL

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 ('بدر','بدر','بدر','بدر'); 2.INSERT dbo.test VALUES ('احمد','احمد','احمد','احمد');
INSERT dbo.test VALUES ('أحمد','أحمد','أحمد','أحمد');
INSERT dbo.test VALUES ('أيات','أيات','أيات','أيات');

And then run these commands:

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_Arabic

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 here a great feature introduced with version of SQL Server 2005 to enable you to write .Net code and by this approach new functions and data types like HIERARCHYID introduced.

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

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;

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 and 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 column because always you need the Gregorian date or create custom data type or use custom functions and this make the integration easy to you because your data stored as standard or with default data type.

 

Regards 
3ART Technologies Database Development Team
http://www.3art.tech