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.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 BETWEEN 20 AND 90) FirstN CROSS JOIN (SELECT * FROM @Tafket WHERE num BETWEEN 1 AND 9) LasteN
INSERT INTO @Tafket  SELECT FirstN.num+LasteN.num,FirstN.NoName+’ و ‘+LasteN.NoName
FROM (SELECT * FROM @Tafket WHERE num BETWEEN 100 AND 900) FirstN CROSS JOIN (SELECT * FROM @Tafket WHERE num BETWEEN 1 AND 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

    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 + @TheNoWithDecimal
END
set @ComWithWord = @ComWithWord + ‘ اردني لا غير’
return  rtrim(@ComWithWord)
end


Regards,
--
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Advertisements

About Mohammad R. Daoud
Mohammad R. Daoud has been working as a Microsoft Dynamics GP consultant since 2004. His career path started with version 7.5; he studied every single tip of the application technicalities and did allot of successful implementations that includes functional consultations, analysis, and custom development projects. He holds a graduate degree in Computer Science and currently he is pursuing MBA degree in Accounting. In January 2007 he was nominated to the Microsoft Most Valuable Professional (MVP) certificate and was certified in April 2008 due to his online contributions in the Dynamics Community (Dynamics GP Newsgroups, Forums, User Groups and his blog: http://mohdaoud.blogspot.com). Worth to mention that he was also listed as one of the Microsoft Dynamics Top 100 Most Influential People in 2009 by DynamicsWorld. And more... • Demonstrated Passion for MS Products through participating in beta testing for some product versions, an effective member of Microsoft Connect. • Talented in securing strong high quality relationships with premier customers, partners and sales teams passionately and profitably. • Dedicated to meeting the expectations and requirements of internal and/or external customers • Problem Solver – Uses systematic approach to uncover true problem(s) and practical solution and can marshal resources to solve problem! • Proven record of effective account management, including Account Planning, Opportunity Management, and Business Management Excellence and working as part of a larger virtual team • Successful in delivering high quality technical engagements in the enterprise Dynamics GP implementations area according to customer requirements. • Dedicated, Committed, Self Motivated, Creative and highly focused with excellent communication, Negotiation, presentation, Active Listening and Objection Handling skills. • Experienced in Profitable researches and studies including white papers and technical presentations.

18 Responses to Convert Numbers To Arabic Words (Tafqeet)

  1. hocine says:

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

  2. Omar says:

    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.

  3. Omar,

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

    Regards,

    Mohammad

  4. Omar says:

    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

  5. Omar,

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

    Regards,

    Mohammad

  6. Omar says:

    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

  7. 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

  8. Omar says:

    Hi,

    thank you you fast replay.

    but one more issue, try this amount (25500.500)

    Many Thanks for your Support .

    Regards
    Omar

  9. Omar says:

    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

  10. Thanks Omar, it should be working now.

    Regards,

    Mohammad

  11. VinothJP says:

    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

  12. VinothJP says:

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

  13. Anonymous says:

    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,

  14. kiranlal says:

    Please send me the latest Script…
    Thanks in Advance.

  15. Anonymous says:

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

  16. Anonymous says:

    السلام عليكم ورحمة الله وبركاتةالدالة المرفقة خاصة بتفقيط المبالغ الي حروف لكني لاحظت ان تفقيط القروش بها مشكلةمثال 3285.25التفقيط الصحيح لها# فقط ثلاثة الاف ومائتان و خمسة و ثمانون جنيها و 25/100 لا غير #لكن نتيجة الدالة التالي# فقط ثلاثة الاف ومائتان و خمسة و ثمانون جنيها ومائتان و خمسون قرش لا غير #وهذا ان الباقي 250 قرش وليس 25 قرشأتمني ان اجد المساعده في هذا الخلل الموجود في هذه الدالة

  17. Anonymous says:

    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

  18. Anonymous says:

    Thank you very much. great work

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: