Monday 13 August 2012

Convert Number into Hindi Words In SQL Server

In SQL Server 2005, 2008, 2012, to Convert Number into Hindi Words, you will need to write a function, because we do not have an inbuilt function which could do the job. Below is the script to create such function which could convert large numbers into the words : 



CREATE FUNCTION
[dbo].[udf_NumberToHindiWords]



(



@InNumericValue      NUMERIC(38,2)



)



RETURNS VARCHAR(1000)



AS



BEGIN







-----*********************************************************







DECLARE @NumericDetails TABLE



       (



        Number       INT



       ,Word         VARCHAR(20)



       )







INSERT INTO
@NumericDetails   SELECT 1, 'Ek'



       UNION ALL SELECT 2, 'Do'          UNION ALL SELECT 3, 'Teen'



       UNION ALL SELECT 4, 'Char'        UNION ALL SELECT 5, 'Paanch'



       UNION ALL SELECT 6, 'Chheh'       UNION ALL SELECT 7, 'Saat'



       UNION ALL SELECT 8, 'Aath'        UNION ALL SELECT 9, 'Nau'



       UNION ALL SELECT 10, 'Dus'        UNION ALL SELECT 11, 'Gyarah'



       UNION ALL SELECT 12, 'Barah'      UNION ALL SELECT 13, 'Terah'



       UNION ALL SELECT 14, 'Choudah'    UNION ALL SELECT 15, 'Pandrah'



       UNION ALL SELECT 16, 'Solah'      UNION ALL SELECT 17, 'Satrah'



       UNION ALL SELECT 18, 'Attharah'   UNION ALL SELECT 19, 'Unnees'



       UNION ALL SELECT 20, 'Bees'       UNION ALL SELECT 21, 'Ikkees'



       UNION ALL SELECT 22, 'Baies'      UNION ALL SELECT 23, 'Teies'



       UNION ALL SELECT 24, 'Choubees'   UNION ALL SELECT 25, 'Pachchees'



       UNION ALL SELECT 26, 'Chhabees'   UNION ALL SELECT 27, 'Sattaies '



       UNION ALL SELECT 28, 'Atthaies'   UNION ALL SELECT 29, 'Untees'



       UNION ALL SELECT 30, 'Tees'       UNION ALL SELECT 31, 'Iktees'



       UNION ALL SELECT 32, 'Battees'    UNION ALL SELECT 33, 'Tetees'



       UNION ALL SELECT 34, 'Choutees'   UNION ALL SELECT 35, 'Pentees'



       UNION ALL SELECT 36, 'Chhattees'  UNION ALL SELECT 37, 'Sentees '



       UNION ALL SELECT 38, 'Adatees'    UNION ALL SELECT 39, 'Unchalees'



       UNION ALL SELECT 40, 'Chalees'    UNION ALL SELECT 41, 'Iktalees'



       UNION ALL SELECT 42, 'Bayalees'   UNION ALL SELECT 43, 'Teralees'



       UNION ALL SELECT 44, 'Chawalees'  UNION ALL SELECT 45, 'Pentalees'



       UNION ALL SELECT 46, 'Chhayalees' UNION ALL SELECT 47, 'Sentalees'



       UNION ALL SELECT 48, 'Adtalees'   UNION ALL SELECT 49, 'Unchas'



       UNION ALL SELECT 50, 'Pachaas'    UNION ALL SELECT 51, 'Ikyawan'



       UNION ALL SELECT 52, 'Bawan'      UNION ALL SELECT 53, 'Trepan'



       UNION ALL SELECT 54, 'Chauwan'    UNION ALL SELECT 55, 'Pachpan'



       UNION ALL SELECT 56, 'Chhappan'   UNION ALL SELECT 57, 'Sattawan'



       UNION ALL SELECT 58, 'Atthawan'   UNION ALL SELECT 59, 'Unsath'



       UNION ALL SELECT 60, 'Saath'      UNION ALL SELECT 61, 'Iksath'



       UNION ALL SELECT 62, 'Basath'     UNION ALL SELECT 63, 'Tresath'



       UNION ALL SELECT 64, 'Chausath'   UNION ALL SELECT 65, 'Pensath'



       UNION ALL SELECT 66, 'Chhiyasath' UNION ALL SELECT 67, 'Sarsath'



       UNION ALL SELECT 68, 'Adsath'     UNION ALL SELECT 69, 'Unhattar'



       UNION ALL SELECT 70, 'Sattar'     UNION ALL SELECT 71, 'Ikhattar'



       UNION ALL SELECT 72, 'Bahattar'   UNION ALL SELECT 73, 'Tehattar'



       UNION ALL SELECT 74, 'Chauhattar' UNION ALL SELECT 75,'Pachhattar'



       UNION ALL SELECT 76, 'Chhiyattar' UNION ALL SELECT 77, 'Satattar'



       UNION ALL SELECT 78, 'Athattar'   UNION ALL SELECT 79, 'Unyasi'



       UNION ALL SELECT 80, 'Assi'       UNION ALL SELECT 81, 'Ikyasi'



       UNION ALL SELECT 82, 'Bayasi'     UNION ALL SELECT 83, 'Terasi'



       UNION ALL SELECT 84, 'Chaurasi'   UNION ALL SELECT 85, 'Pachasi'



       UNION ALL SELECT 86, 'Chhiyasi'   UNION ALL SELECT 87, 'Satasi'



       UNION ALL SELECT 88, 'Athasi'     UNION ALL SELECT 89, 'Nawasi'



       UNION ALL SELECT 90, 'Nabbe'      UNION ALL SELECT 91, 'Inkyanwe'



       UNION ALL SELECT 92, 'Bannawe'    UNION ALL SELECT 93, 'Terannwe'



       UNION ALL SELECT 94, 'Chaurannwe' UNION ALL SELECT 95, 'Pachannwe'



       UNION ALL SELECT 96, 'Chhiyannwe' UNION ALL SELECT 97, 'Sattannwe'



       UNION ALL SELECT 98, 'Atthannvwe' UNION ALL SELECT 99, 'Ninnanwe'











DECLARE @DigitDetails TABLE



       (



        DigitPlace   INT



        ,PlaceName   VARCHAR(30)



       )







INSERT INTO
@DigitDetails   SELECT
1, 'Sau'



       UNION ALL SELECT 2, 'Hazar'     UNION ALL SELECT 3, 'Lakh'



       UNION ALL SELECT 4, 'Crore'     UNION ALL SELECT 5, 'Arab'



       UNION ALL SELECT 6, 'Kharab'    UNION ALL SELECT 7, 'Neel'



       UNION ALL SELECT 8, 'Padma'     UNION ALL SELECT 9, 'Shankh'



       UNION ALL SELECT 10, 'Ald'      UNION ALL SELECT 11, 'Ank'



       UNION ALL SELECT 12, 'Jald'     UNION ALL SELECT 13, 'Madh'



       UNION ALL SELECT 14, 'Parardha' UNION ALL SELECT 15, 'Ant'



       UNION ALL SELECT 16, 'Maha Ant' UNION ALL SELECT 17, 'Shisht'



       UNION ALL SELECT 18, 'Singhar' UNION ALL SELECT 19, 'Maha Singhar'



       UNION ALL SELECT 20, 'Adant Singhar'











-----**************************************************************







DECLARE        @StrNumber          VARCHAR(60)



              ,@LargeNumber        VARCHAR(60)



              ,@SmallNumber        VARCHAR(10)



              ,@DecimalNumber      VARCHAR(10)



              ,@Chunk              VARCHAR(50)



              ,@ChunkVal           INT



              ,@StrFinal           VARCHAR(1000)



              ,@StrLength          INT



              ,@DigitLoop          INT











SET @DecimalNumber = ''



SET @StrFinal = ''







SET @DigitLoop = 2







SET @StrNumber = ABS(@InNumericValue)



SET @StrNumber = SUBSTRING(@StrNumber,1,CASE



WHEN CHARINDEX('.',@StrNumber)=0 THEN LEN(@StrNumber)



                     ELSE
CHARINDEX('.', @StrNumber)-1 END)   











SET @StrLength = LEN(@StrNumber)



IF(@StrLength > 3)



BEGIN



    SET
@LargeNumber = SUBSTRING(@StrNumber,-2,@StrLength)



    SET @StrLength  = LEN(@StrNumber)







    WHILE LEN(@LargeNumber) > 0



    BEGIN



        SET @Chunk = ''



        SET @ChunkVal = CAST(RIGHT(@LargeNumber,2) AS INT)



        BEGIN



           
SELECT @Chunk =
WORD FROM @NumericDetails



               
WHERE NUMBER =
@ChunkVal



           
SELECT @Chunk=@Chunk+' '+PlaceName FROM
@DigitDetails



               
WHERE DigitPlace = @DigitLoop







           
IF(@ChunkVal) <> 0



           
SET @StrFinal =
@Chunk + ', ' + @StrFinal



           
SET @StrLength =
LEN(@LargeNumber)



           
SET @LargeNumber=SUBSTRING(@LargeNumber,-1,@StrLength)



           
SET @DigitLoop =
@DigitLoop + 1



        END



   END



   SET @StrLength = 3



   SET @StrFinal = RTRIM(@StrFinal)



   SET @StrFinal = STUFF(@StrFinal,LEN(@StrFinal),1,'')



END







IF(@StrLength = 3)



BEGIN



       SET
@Chunk = ''



       SET @SmallNumber
= RIGHT(@StrNumber,3)



       SET @ChunkVal
= CAST(LEFT(@SmallNumber,1) AS INT)



       IF (@ChunkVal > 0)



       BEGIN



              SELECT @Chunk=Word+' '+'Sau' FROM @NumericDetails



                     WHERE
NUMBER = @ChunkVal



              IF(@StrFinal <> '')



              SET
@StrFinal = @StrFinal +
', ' + @Chunk



              ELSE



              SET
@StrFinal = @Chunk



       END



       SET
@StrLength = 2



END







IF(@StrLength < 3 AND @StrLength > 0)



BEGIN



       SET
@Chunk = ''



       SET @SmallNumber
= RIGHT(@StrNumber,2)



       SET @ChunkVal
= CAST(LEFT(@SmallNumber,2) AS INT)







       IF(@ChunkVal > 0)



       BEGIN



              SELECT
@Chunk = WORD FROM
@NumericDetails



                     WHERE
NUMBER = @ChunkVal



              IF(@StrFinal <> '')



              SET
@StrFinal = @StrFinal +
', ' + @Chunk



              ELSE



              SET
@StrFinal = @Chunk



       END







END







-----*******************************************************







SELECT @StrFinal = ISNULL(@StrFinal,'')



       



RETURN @StrFinal







-----*******************************************************







END



   Check the output below :



SELECT dbo.[udf_NumberToHindiWords] (55000409732) AS [Output]


--Output :


Pachpan Arab, Char Lakh, Nau Hazar, Saat Sau, Battees 



================================================================ ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo ================================================================

 
If we want Partial Hindi Words, then we have to make some modifications and don't need to insert @NumericDetails upto 99, see below : 



CREATE FUNCTION
[dbo].[udf_NumberToWords]



(



@InNumericValue      NUMERIC(38,2)



)



RETURNS VARCHAR(1000)



AS



BEGIN







-----*********************************************************







DECLARE @NumericDetails TABLE



       (



        Number       INT



       ,Word         VARCHAR(20)



       )







INSERT INTO
@NumericDetails   SELECT 1, 'One'



       UNION ALL SELECT 2, 'Two'       UNION ALL SELECT 3, 'Three'



       UNION ALL SELECT 4, 'Four'      UNION ALL SELECT 5, 'Five'



       UNION ALL SELECT 6, 'Six'       UNION ALL SELECT 7, 'Seven'



       UNION ALL SELECT 8, 'Eight'     UNION ALL SELECT 9, 'Nine'



       UNION ALL SELECT 10, 'Ten'      UNION ALL SELECT 11, 'Eleven'



       UNION ALL SELECT 12, 'Twelve'   UNION ALL SELECT 13, 'Thirteen'



       UNION ALL SELECT 14, 'Fourteen' UNION ALL SELECT 15, 'Fifteen'



       UNION ALL SELECT 16, 'Sixteen'  UNION ALL SELECT 17, 'Seventeen'



       UNION ALL SELECT 18, 'Eighteen' UNION ALL SELECT 19, 'Nineteen'



       UNION ALL SELECT 20, 'Twenty'   UNION ALL SELECT 30, 'Thirty'



       UNION ALL SELECT 40, 'Forty'    UNION ALL SELECT 50, 'Fifty'



       UNION ALL SELECT 60, 'Sixty'    UNION ALL SELECT 70, 'Seventy'



       UNION ALL SELECT 80, 'Eighty'   UNION ALL SELECT 90, 'Ninety'















DECLARE @DigitDetails TABLE



       (



        DigitPlace   INT



        ,PlaceName   VARCHAR(30)



       )







INSERT INTO
@DigitDetails   SELECT
1, 'Sau'



       UNION ALL SELECT 2, 'Hazar'     UNION ALL SELECT 3, 'Lakh'



       UNION ALL SELECT 4, 'Crore'     UNION ALL SELECT 5, 'Arab'



       UNION ALL SELECT 6, 'Kharab'    UNION ALL SELECT 7, 'Neel'



       UNION ALL SELECT 8, 'Padma'     UNION ALL SELECT 9, 'Shankh'



       UNION ALL SELECT 10, 'Ald'      UNION ALL SELECT 11, 'Ank'



       UNION ALL SELECT 12, 'Jald'     UNION ALL SELECT 13, 'Madh'



       UNION ALL SELECT 14, 'Parardha' UNION ALL SELECT 15, 'Ant'



       UNION ALL SELECT 16, 'Maha Ant' UNION ALL SELECT 17, 'Shisht'



       UNION ALL SELECT 18, 'Singhar' UNION ALL SELECT 19, 'Maha Singhar'



       UNION ALL SELECT 20, 'Adant Singhar'











-----**************************************************************







DECLARE        @StrNumber          VARCHAR(60)



              ,@LargeNumber        VARCHAR(60)



              ,@SmallNumber        VARCHAR(10)



              ,@DecimalNumber      VARCHAR(10)



              ,@Chunk              VARCHAR(50)



              ,@ChunkVal           INT



              ,@TenthVal           INT



              ,@UnitVal            INT



              ,@StrFinal           VARCHAR(1000)



              ,@StrLength          INT



              ,@DigitLoop          INT











SET @DecimalNumber = ''



SET @StrFinal = ''







SET @DigitLoop = 2







SET @StrNumber = ABS(@InNumericValue)



SET @StrNumber = SUBSTRING(@StrNumber,1,CASE



WHEN CHARINDEX('.',@StrNumber)=0 THEN LEN(@StrNumber)



                     ELSE
CHARINDEX('.', @StrNumber)-1 END)   











SET @StrLength = LEN(@StrNumber)



IF(@StrLength > 3)



BEGIN



    SET
@LargeNumber = SUBSTRING(@StrNumber,-2,@StrLength)



    SET @StrLength  = LEN(@StrNumber)







    WHILE LEN(@LargeNumber) > 0



    BEGIN



        SET @Chunk = ''



        SET @ChunkVal = CAST(RIGHT(@LargeNumber,2) AS INT)



        IF (@ChunkVal > 20)



        BEGIN



            SET @TenthVal = (@ChunkVal/10)*10



            SET
@UnitVal = @ChunkVal%10



           
SELECT @Chunk =
WORD FROM @NumericDetails



                WHERE NUMBER =
@TenthVal



           
SELECT @Chunk=@Chunk
+' '+ WORD FROM
@NumericDetails



                WHERE NUMBER = @UnitVal



           
SELECT @Chunk=@Chunk+' '+PlaceName FROM
@DigitDetails



                WHERE
DigitPlace = @DigitLoop







           
SET @StrFinal =
@Chunk + ', ' + @StrFinal



           
SET @StrLength =
LEN(@LargeNumber)



           
SET @LargeNumber=SUBSTRING(@LargeNumber,-1,@StrLength)



           
SET @DigitLoop =
@DigitLoop + 1



        END



        ELSE



        BEGIN



           
SELECT @Chunk =
WORD FROM @NumericDetails



               
WHERE NUMBER =
@ChunkVal



           
SELECT @Chunk=@Chunk+' '+PlaceName FROM
@DigitDetails



               
WHERE DigitPlace = @DigitLoop







           
IF(@ChunkVal) <> 0



           
SET @StrFinal =
@Chunk + ', ' + @StrFinal



           
SET @StrLength =
LEN(@LargeNumber)



           
SET @LargeNumber=SUBSTRING(@LargeNumber,-1,@StrLength)



           
SET @DigitLoop =
@DigitLoop + 1



        END



   END



   SET @StrLength = 3



   SET @StrFinal = RTRIM(@StrFinal)



   SET @StrFinal = STUFF(@StrFinal,LEN(@StrFinal),1,'')







END











IF(@StrLength = 3)



BEGIN



       SET
@Chunk = ''



       SET @SmallNumber
= RIGHT(@StrNumber,3)



       SET @ChunkVal
= CAST(LEFT(@SmallNumber,1) AS INT)



       IF (@ChunkVal > 0)



       BEGIN



              SELECT @Chunk=Word+' '+'Sau' FROM @NumericDetails



                     WHERE
NUMBER = @ChunkVal



              IF(@StrFinal <> '')



              SET
@StrFinal = @StrFinal +
', ' + @Chunk



              ELSE



              SET
@StrFinal = @Chunk



       END



       SET
@StrLength = 2



END







IF(@StrLength < 3 AND @StrLength > 0)



BEGIN



       SET
@Chunk = ''



       SET @SmallNumber
= RIGHT(@StrNumber,2)



       SET @ChunkVal
= CAST(LEFT(@SmallNumber,2) AS INT)







       IF (@ChunkVal > 20)



       BEGIN



              SET
@TenthVal = (@ChunkVal/10)*10



              SET
@UnitVal = @ChunkVal%10



              SELECT
@Chunk = WORD FROM
@NumericDetails



                     WHERE
NUMBER = @TenthVal



              SELECT
@Chunk=@Chunk+' '+WORD FROM @NumericDetails



                     WHERE
NUMBER = @UnitVal



              IF(@StrFinal <> '')



              SET
@StrFinal = @StrFinal +
', ' + @Chunk



              ELSE



              SET
@StrFinal = @Chunk



       END



       ELSE IF(@ChunkVal <= 20 AND
@ChunkVal > 0)



       BEGIN



              SELECT
@Chunk = WORD FROM
@NumericDetails



                     WHERE
NUMBER = @ChunkVal



              IF(@StrFinal <> '')



              SET
@StrFinal = @StrFinal +
', ' + @Chunk



              ELSE



              SET
@StrFinal = @Chunk



       END



END







-----*******************************************************







SELECT @StrFinal = ISNULL(@StrFinal,'')



RETURN @StrFinal







-----*******************************************************





END



   Check the output below :



SELECT dbo.[udf_NumberToWords] (58000409732) AS [Output]



--Output :


Fifty Eight Arab, Four Lakh, Nine Hazar, Seven Sau, Thirty Two






 Reference: Govind Badkur(http://sqlserver20.blogspot.com)
Posted by at
Labels:
Location: R-Tech Park, Nirlon Compound, Goregaon East, Mumbai, Maharashtra, India.

4 comments:

  1. Ravindra20 November 2019 at 10:43

    Ver Nice Bro,Thanks For sharing,is it possible to modify it bit, like if we want to display 500 Crore instead of 5 Arab, how we can do that

    ReplyDelete
    Replies
    1. Govind4 December 2019 at 20:51

      Below link could be helpful :
      https://sqlserver20.blogspot.com/2012/08/convert-number-into-words.html

      Delete
  2. Ravindra20 November 2019 at 10:44

    kindly help with it

    ReplyDelete
  3. shailendra sen7 November 2023 at 01:40

    can we convert english name in hindi UTF8

    ReplyDelete

Subscribe to: Post Comments (Atom)