SQL Server and Arabic language

I have read some articles written about how some products like Office 2010 by default support not only Hijri Date but also support 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:


01.CREATE TABLE [dbo].[Test](
02.--here stored data as ASCII char
03.[Name_Char_Latain] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
04.--here stored data as Unicode
05.[Name_nChar_Latain] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
06.--here stored data as ASCII char
07.[Name_Char_Arabic] [char](10) COLLATE Arabic_CI_AI NULL,
08.--here stored data as Unicode
09.[Name_nChar_Arabic] [nchar](10) COLLATE Arabic_CI_AI NULL
10.)
11.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:

1.USE [master]
2.GO
3.ALTER DATABASE [TestDb] COLLATE Arabic_100_CI_AI
4.GO

Or you can write your statement like this


1.INSERT dbo.test VALUES (N'بدر',N'بدر',N'بدر',N'بدر');
After we created the table then inserts these records
1.INSERT dbo.test VALUES ('بدر','بدر','بدر','بدر');
2.INSERT dbo.test VALUES ('احمد','احمد','احمد','احمد');
3.INSERT dbo.test VALUES ('أحمد','أحمد','أحمد','أحمد');
4.INSERT dbo.test VALUES ('أيات','أيات','أيات','أيات');


And then run these commands:

1.SELECT * FROM dbo.test ORDER BY Name_Char_Latain
2.SELECT * FROM dbo.test ORDER BY Name_nChar_Latain
3.SELECT * FROM dbo.test ORDER BY Name_Char_Arabic
4.SELECT * FROM dbo.test ORDER BY Name_nChar_Arabic
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:

1.SELECT CONVERT(char(40),GETDATE(),130)
2.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;
5. The complete code:
01.using System.Data.SqlTypes;
02.using Microsoft.SqlServer.Server;
03.using System.Globalization;
04.
05.public partial class UserDefinedFunctions
06.{
07.[Microsoft.SqlServer.Server.SqlFunction]
08.public static SqlString UmAlQuraConverter(DateTime date, string format)
09.{
10.return new SqlString(getUmAlQuraDate(date, format));
11.}
12.
13.static string getUmAlQuraDate(DateTime date, string format)
14.{
15.UmAlQuraCalendar umAlQuraCal = new UmAlQuraCalendar();
16.CultureInfo umAlQuraCulture = new CultureInfo("ar-SA");
17.umAlQuraCulture.DateTimeFormat.Calendar = umAlQuraCal;
18.return date.ToString(format, umAlQuraCulture);
19.}
20.};

Then choose deploy solution and Then run this command to enable using CLR in your SQL server instance
1.EXEC sp_configure 'clr enabled', 1;
2.RECONFIGURE;

Then run this command to get UmAlQura Date
1.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
Rashid Imran Bilgrami
www.bestvisualization.com