Saturday, October 25, 2008

Convert Numbers To Arabic Words (Tafqeet)

Function to Convert Numbers To Arabic Words (Tafqeet):

You Can Call This Function By:

Select Tafkeet(9875)

Code Below:

CREATE FUNCTION [dbo].[DI_Tafkeet] (@TheNo  numeric(18,3))
returns varchar(1000) as


 
begin
if @TheNo <= 0   return 'zero'

declare @TheNoAfterReplicate varchar(15)
set @TheNoAfterReplicate = right(replicate('0',15)+cast(floor(@TheNo) as varchar(15)),15)
declare @ComWithWord varchar(1000),@TheNoWithDecimal as varchar(400),@ThreeWords as int
set @ThreeWords=0
set @ComWithWord  = ' فقط '
declare   @Tafket TABLE (num int,  NoName varchar(100))
INSERT INTO @Tafket VALUES (0,'') 
INSERT INTO @Tafket VALUES (1,'واحد')
INSERT INTO @Tafket VALUES (2,'اثنان')
INSERT INTO @Tafket VALUES (3,'ثلاثة')
INSERT INTO @Tafket VALUES (4,'اربعة')
INSERT INTO @Tafket VALUES (5,'خمسة')
INSERT INTO @Tafket VALUES (6,'ستة')
INSERT INTO @Tafket VALUES (7,'سبعة')
INSERT INTO @Tafket VALUES (8,'ثمانية')
INSERT INTO @Tafket VALUES (9,'تسعة')
INSERT INTO @Tafket VALUES (10,'عشرة')
INSERT INTO @Tafket VALUES (11,'احدى عشر')
INSERT INTO @Tafket VALUES (12,'اثنى عشر')
INSERT INTO @Tafket VALUES (13,'ثلاثة عشر')
INSERT INTO @Tafket VALUES (14,'اربعة عشر')
INSERT INTO @Tafket VALUES (15,'خمسة عشر')
INSERT INTO @Tafket VALUES (16,'ستة عشر')
INSERT INTO @Tafket VALUES (17,'سبعة عشر')
INSERT INTO @Tafket VALUES (18,'ثمانية عشر')
INSERT INTO @Tafket VALUES (19,'تسعة عشر')
INSERT INTO @Tafket VALUES (20,'عشرون')
INSERT INTO @Tafket VALUES (30,'ثلاثون')
INSERT INTO @Tafket VALUES (40,'اربعون')
INSERT INTO @Tafket VALUES (50,'خمسون')
INSERT INTO @Tafket VALUES (60,'ستون')
INSERT INTO @Tafket VALUES (70,'سبعون')
INSERT INTO @Tafket VALUES (80,'ثمانون')
INSERT INTO @Tafket VALUES (90,'تسعون')
INSERT INTO @Tafket VALUES (100,'مائة')
INSERT INTO @Tafket VALUES (200,'مائتان')
INSERT INTO @Tafket VALUES (300,'ثلاثمائة')
INSERT INTO @Tafket VALUES (400,'أربعمائة')
INSERT INTO @Tafket VALUES (500,'خمسمائة')
INSERT INTO @Tafket VALUES (600,'ستمائة')
INSERT INTO @Tafket VALUES (700,'سبعمائة')
INSERT INTO @Tafket VALUES (800,'ثمانمائة')
INSERT INTO @Tafket VALUES (900,'تسعمائة')
INSERT INTO @Tafket 
SELECT FirstN.num+LasteN.num,LasteN.NoName+' و '+FirstN.NoName FROM
(SELECT * FROM @Tafket WHERE num >= 20 AND num <= 90) FirstN
CROSS JOIN
(SELECT * FROM @Tafket WHERE num >= 1 AND num <= 9) LasteN

INSERT INTO @Tafket 
SELECT FirstN.num+LasteN.num,FirstN.NoName+' و '+LasteN.NoName FROM (SELECT * FROM @Tafket WHERE num >= 100 AND num <= 900) FirstN
CROSS JOIN
(SELECT * FROM @Tafket WHERE num >= 1 AND num <= 99) LasteN


if left(@TheNoAfterReplicate,3) > 0
set @ComWithWord = @ComWithWord + ISNULL((select NoName  from  @Tafket where num=left(@TheNoAfterReplicate,3)),'')+  ' ترليون'
if left(right(@TheNoAfterReplicate,12),3) > 0 and  left(@TheNoAfterReplicate,3) > 0
set @ComWithWord=@ComWithWord+ ' و '
if left(right(@TheNoAfterReplicate,12),3) > 0
set @ComWithWord = @ComWithWord +ISNULL((select NoName from @Tafket where num=left(right(@TheNoAfterReplicate,12),3)),'') +  ' بليون'
if left(right(@TheNoAfterReplicate,9),3) > 0

begin
set @ComWithWord=@ComWithWord + case  when @TheNo>999000000  then ' و'  else '' end
set @ThreeWords=left(right(@TheNoAfterReplicate,9),3)
set @ComWithWord = @ComWithWord + ISNULL((select case when   @ThreeWords>2 then NoName end  from @Tafket  where num=left(right(@TheNoAfterReplicate,9),3)),'')  + case when  @ThreeWords=2 then ' مليونان' when   @ThreeWords between 3 and 10 then ' ملايين' else ' مليون' end
end

if left(right(@TheNoAfterReplicate,6),3) > 0
begin
set @ComWithWord=@ComWithWord + case  when @TheNo>999000  then ' و'  else '' end
set @ThreeWords=left(right(@TheNoAfterReplicate,6),3)
set @ComWithWord = @ComWithWord + ISNULL((select case when  @ThreeWords>2 then NoName  end from @Tafket where num=left(right(@TheNoAfterReplicate,6),3)),'')+ case when  @ThreeWords=2 then ' الفان' when @ThreeWords between 3 and 10 then ' الاف'  else ' الف' end
end

if right(@TheNoAfterReplicate,3) > 0
begin

if @TheNo>999
begin
set @ComWithWord=@ComWithWord + ' و'
end

if right(@TheNoAfterReplicate, 2) = '01' or right(@TheNoAfterReplicate, 2) = '02'
begin
--set @ComWithWord=@ComWithWord + case  when @TheNo>1000  then ' و'  else '' end
--set @ThreeWords=left(right(@TheNoAfterReplicate,6),3)
set @ComWithWord = @ComWithWord + ' ' + ISNULL((select noname from @Tafket where num=right(@TheNoAfterReplicate, 3)),'')
end

set @ThreeWords=right(@TheNoAfterReplicate,2)

if @ThreeWords=0
begin
--   set @ComWithWord=@ComWithWord + ' و'
   set @ComWithWord = @ComWithWord + ISNULL((select NoName  from @Tafket where @ThreeWords=0 AND num=right(@TheNoAfterReplicate,3)),'')
end

end

set @ThreeWords=right(@TheNoAfterReplicate,2)
set @ComWithWord =  @ComWithWord  +   ISNULL((select  NoName  from @Tafket where @ThreeWords>2 AND num=right(@TheNoAfterReplicate,3)),'')
set @ComWithWord = @ComWithWord +' '+ case when  @ThreeWords=2 then ' ديناران' when @ThreeWords between 3 and 10 then ' دنانير'  else ' دينار' end
if right(rtrim(@ComWithWord),1)=',' set @ComWithWord = substring(@ComWithWord,1,len(@ComWithWord)-1)
if  right(@TheNo,len(@TheNo)-charindex('.',@TheNo)) >0 and charindex('.',@TheNo)<>0
    begin
        set @ThreeWords=left(right(round(@TheNo,3),3),3)
        SELECT @TheNoWithDecimal=  ' و' + ISNULL((SELECT NoName from @Tafket where num=left(right(round(@TheNo,3),3),3)  AND @ThreeWords >3),'')
        set @TheNoWithDecimal = @TheNoWithDecimal+  case when  @ThreeWords=2 then ' فلسان' when @ThreeWords between 3 and 10 then ' فلسات'  else '  فلس' end
set @ComWithWord = @ComWithWord + ' و '+ CONVERT(varchar(max),@ThreeWords)+ case when  @ThreeWords=2 then ' فلسان' when @ThreeWords between 3 and 10 then ' فلسات'  else '  فلس' end --@TheNoWithDecimal
END
set @ComWithWord = @ComWithWord + ' لا غير '

return rtrim(@ComWithWord)
end


GO

UPDATE: Fixed some issues in covering numbers (101, 201, 301 …)
UPDATE: Fixed decimals not to be included as per many requests.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

35 comments:

hocine said...

السلام عليكم،
أشكركم على الجهد الذي تبدلونه، ولكن مشكلتي مع التفقيط أن الخط الذي كتبتم به غير متواجد عندي،
لذلك لأطلب من سيادتكم أن تكتبوا بخط من إنشاء شركة ميكروسوفت حتى يسهل استخدامه من طرف الجميع، بدل البحث عن الخط.
شكرا
bimobimo2001@yahoo.fr

Omar said...

Hi,

the function is not working well, need some modification. and they are:

change varchar to nvarchar for all the lines. also we need to put N in fron any arabic words. as you can see below:
ate,3)),'')+ N' ترليون'

and with all these change it's still not working well, it's not displaying the words for 100 to 900 and also not displaying the words for 101, 102.
so i hope from the publisher to help in rectifies the problem at is very useful function for us, but its useless if it's not working well.

Mohammad R. Daoud said...

Omar,

Thanks and appreciate your feedback, I will validate your comments and getback to you.

Regards,

Mohammad

Omar said...

Hi Mohammad,

Please do fix it, as it is very usefully for us to have such a good function to work with.
Many thanks for the great Script.

Regards,
Omar

Mohammad R. Daoud said...

Omar,

I have modified the query to resolve the issue you found, thanks for pointing me to this issue.

Regards,

Mohammad

Omar said...

Hi,

thank you..


But try to input the folowing amount (110) and see what comes out.

the 100 are solved but now new issue the 110 or 120 and see whay comes out

Please give us your feed back

Regards
Omar

Mohammad R. Daoud said...

Thanks again Omar, I did modify the script and fixed the new issue, please let me know if you have any other comments.

Regards,

Mohammad

Omar said...

Hi,

thank you you fast replay.

but one more issue, try this amount (25500.500)

Many Thanks for your Support .

Regards
Omar

Omar said...

NOW IT WORKS VERY FINE.. Congratulation

You are DAM good SQL Programmer.

Many Thanks for the great script ....

P.S. I will contact if I need any help ;-)

Regards
Omar

Mohammad R. Daoud said...

Thanks Omar, it should be working now.

Regards,

Mohammad

VinothJP said...

Function provided here is the latest updated one? If not could you please send the latest one. I am trying to convert your SQL function to Oracle function. Thanks for your effort. Thanks in advance

VinothJP said...

Could you please update the latest version of the function in the blog?

Anonymous said...

Hi,
Thx for the script. it is very useful, but i was wondering if it is the latest update. actually, I was trying to convert the numbers 202,201,1501 or similar . it is always missing the 'hundred' part.

Thank you in advance
best regards,

kiranlal said...

Please send me the latest Script...
Thanks in Advance.

Anonymous said...

أشكرك يا أستاذ محمود على الجهد الرائع ولكن هل من الممكن كيفية إيضاح طريقة عملها في داخل sql

Anonymous said...

السلام عليكم ورحمة الله وبركاتة

الدالة المرفقة خاصة بتفقيط المبالغ الي حروف لكني لاحظت ان تفقيط القروش بها مشكلة

مثال 3285.25

التفقيط الصحيح لها

# فقط ثلاثة الاف ومائتان و خمسة و ثمانون جنيها و 25/100 لا غير #

لكن نتيجة الدالة التالي

# فقط ثلاثة الاف ومائتان و خمسة و ثمانون جنيها ومائتان و خمسون قرش لا غير #

وهذا ان الباقي 250 قرش وليس 25 قرش

أتمني ان اجد المساعده في هذا الخلل الموجود في هذه الدالة

Anonymous said...

Salaamalaikum Mohammad,

I m looking for Excel VB Code for the same topic i mean "convert numbers to Arabic words. It will be a great help if u provide me the script to my email address... shaikmohammed81@yahoo.com

Anonymous said...

Thank you very much. great work

Anonymous said...

Hi guys , can someone send me the function Tafqeet that works in MS Access 2003 please ??

Unknown said...

السلام عليكم

how can convert number to Arabic text in MS- word like
100 = مائة

mohamed
Mohamedchehemi16@gmail.com

Anonymous said...

Dear Mohammad R. Daoud,

We are using shared sql function for converting numbers into Arabic words please check below .
Can you please modify this function as per below condition

201 should be مئتان و ريال عماني
instead of مائتان و واحد ريالاً عمانياً


202 should be مئتان وريالان عماني
instead of مائتان و إثنان ريالاً عمانياً

203 should be مئتان وثلاث ريالات عماني
instead of مائتان و ثلاثة ريالات عمانية


similar,

301 should be ثلاثة مئة و ريال عماني
instead of ثلاثمائة و واحد ريالاً عمانياً


302 should be ثلاثة مئة و ريالان عماني
instead of ثلاثمائة و إثنان ريالاً عمانياً

303 should be ثلاثة مئة وثلاث ريالات عماني
instead of ثلاثمائة و ثلاثة ريالات عمانية


Similar this manner translate should be for 401,501,601,2001,2002,2003,3001,3002,3003.....etc
hope you understand my requirement so please do the needful asap.


Thanks and Regards
Noor Alam
noor@iitcoman.com

Ahmed said...
This comment has been removed by the author.
Ismail Boukhair Blog said...

hi! I really like it I was looking for it for long time
would u mail it to me bcuz this one is not working

btw I added it like this :
https://www.youtube.com/watch?v=fYAxYtzBi5A

Mohammad R. Daoud said...

Below article for Excel:

http://mohdaoud.blogspot.com/2014/08/arabic-english-amount-in-words-for-excel.html

Anonymous said...

please its not working, could you please send me the correct codes.

Thanks

sarah.ehab25@gmail.com

NITU said...

Try...
Free Excel Add-in Convert Excel cell Numbers to Words with Prefix and Suffix features.
Download Link - http://www.xltool.in

Anonymous said...

great function
thank you

Anonymous said...

السلام عليكم
شكرا على المجهود الطيب


عند كتابة الارقام

99999.9 =مائة الف دينارا و 900 فلسا
9999.9==عشرة آلاف دينارا و 900 فلسا
999.9 ==. الف دينارا و 900 فلسا
99.9 = مائة دينارا و 900 فلسا
9.9= عشرة دينارا و 900 فلسا

و هكذا يقوم باضفة واحد الى الرقم

Syed Irfan Ahmed said...

Assalam Alekum,

I'm in badly need of this function for one of my code.
Can anyone please provide me the working & modified version of this.

Any help is appreciated.

Please do send me the code on irfanahmed112@hotmail.com

Thanks.

NITU said...

Hi, Thanks.
User can also try free GWORD addin formula which auto convert numbers or amount to words or rupees.
Download Link - www.xltool.in | www.gwordformula.blogspot.com

NITU said...

Hi, Thanks.
User can also try NEW free GWORD addin formula which auto convert numbers or amount to words or rupees.
Download Link - www.xltool.in | www.gwordformula.blogspot.com

Anonymous said...



Salam Alykum!

The converting of numbers to Arabic text is AWESOME! I am amazed! and grateful for great minds like yours!

Can you please share with me the template with the macro? I tried to find it / download it from your site, but was unable to.

countryfan_nt@yahoo.com

Nawaf

Thank you very very much!

Unknown said...

can you coded to c# windows form
thank you

Anonymous said...

is this code is updated ???
because it is giving error when execute in sql server

Anonymous said...

Add this Command before End Function to solve the Unicode problem

convertANSIArabic2Unicode = sUnicode

Related Posts:

Related Posts with Thumbnails