Skip to main content

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

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]

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;
05.public partial class UserDefinedFunctions
08.public static SqlString UmAlQuraConverter(DateTime date, string format)
10.return new SqlString(getUmAlQuraDate(date, format));
13.static string getUmAlQuraDate(DateTime date, string format)
15.UmAlQuraCalendar umAlQuraCal = new UmAlQuraCalendar();
16.CultureInfo umAlQuraCulture = new CultureInfo("ar-SA");
17.umAlQuraCulture.DateTimeFormat.Calendar = umAlQuraCal;
18.return date.ToString(format, umAlQuraCulture);

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;

Then run this command to get UmAlQura Date
1.SELECT dbo.UmAlQuraConverter(GETDATE(),'dd/MM/yyyy')
And you will get result as UmAlQura Date.

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.

Rashid Imran Bilgrami


Popular posts from this blog

How to convert and crack windows server 2012 from Evaluation to Full

Dear All

This is a way how you Convert Evalution to Full


Open CMD and run following command
DISM /online /Get-CurrentEdition

<edition ID> is like ServerStandard with out Eval

Step 2:
DISM /online /Set-Edition:<edition ID> /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula

Serial Key

Windows Server 2012
DataCenter: 48HP8-DN98B-MYWDG-T2DCC-8W83P
Datacenter: Y4TGP-NPTV9-HTC2H-7MGQ3-DV4TW

Standard: XC9B7-NBPP2-83J2H-RHMBY-92BT4

Server Essentials:K2XGM-NMBT3-2R6Q8-WF2FK-P36R2

For Standard R2 here is a command
For R2 its like that
DISM /online /Set-Edition:ServerStandard /ProductKey:DBGBW-NPF86-BJVTX-K3WKJ-MTB6V /AcceptEula


Calling LoadLibraryEx on ISAPI filter “C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_filter.dll” failed

Dear Readers
if you get the error

Calling LoadLibraryEx on ISAPI filter “C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_filter.dll” failed

Here is the solution 
Get a command prompt in administrator mode and go to C:\Windows\Microsoft .NET\FrameWork64\
and run aspnet_regiis -r. This will re-register the right libraries. It has happened twice already to me.
For other frameworks (32 bit or other versions of .net make sure you go to the correct folder (i.e. Framework/, etc.)

xxxx is the the number which is present with the folder name so i suggest you guys to check the folder name in your directory

If "run aspnet_regiis -r" is not working and if you get the error run is not recognized command then just type " aspnet_regiis -r" it will run fine

Reason of problem
Your frame work has been change may be you have install the lower version on the higher version or may be because of any reason you have register another version in your IIS, in my cas…

RegisterStartupScript updated way to right Java script JS (Javascript) in code behind also work with update panel

Dear All
RegisterStartupScript is obsolete now the new way that works with update panel  is given below its is code behind file code also

VB Code Dim csname1 As String = "PopupScript"
Dim cstype As Type = Me.GetType()
Dim cs As ClientScriptManager = Page.ClientScript
Dim cstext1 As String = "alert('Your message');"
cs.RegisterStartupScript(cstype, csname1, cstext1, True)

C# Code
String csname1 = "PopupScript";
Type cstype = this.GetType();
ClientScriptManager cs = Page.ClientScript;
String cstext1 = "alert('Hello World');"; cs.RegisterStartupScript(cstype, csname1, cstext1, true);
I hope it will help you also 
Regards  Rashid Imran Bilgrami