Convert Number into Hindi Words in SQL Server

One of my friend is working in SSRS and he got the requirement to print the total sales amount in the report. To fulfill this requirement we have created a user-defined function to convert the total amount in the words.

Read more about User defined function here:  User define function (UDF) in sql server

https://bageshkumarbagi-msbi.blogspot.com/2017/08/user-define-function-udf-in-sql-server.html

Creating function using below script

ALTER FUNCTION [dbo].[udf_NumberToHindiWords] ( @InNumericValue      NUMERIC(38,2) ) RETURNS NVARCHAR(2000) AS BEGIN DECLARE @NumericDetails TABLE        (         Number       INT        ,Word         NVARCHAR(20)        ) INSERT INTO @NumericDetails VALUES (1,'Ek'),(2,'Do'),(3,'Tin'),(4,'Char'),(5,'Paanch'),(6,'Chheh'),(7,'Saat'),(8,'Aath'),(9,'Nau') ,(10,'Dus'),(11,'Gyarah'),(12,'Barah'),(13,'Terah'),(14,'Choudah'),(15,'Pandrah'),(16,'Solah'),(17,'Satrah') ,(18,'Attharah'),(19,'Unnees'),(20,'Bees'),(21,'Ikkees'),(22,'Baies'),(23,'Teies'),(24,'Choubees'),(25,'Pachchees') ,(26,'Chhabees'),(27,'Sattaies'),(28,'Atthaies'),(29,'Untees'),(30,'Tees'),(31,'Iktees'),(32,'Battees'),(33,'Tetees') ,(34,'Choutees'),(35,'Pentees'),(36,'Chhattees'),(37,'Sentees'),(38,'Adatees'),(39,'Unchalees'),(40,'Chalees'),(41,'Iktalees') ,(42,'Bayalees'),(43,'Teralees'),(44,'Chawalees'),(45,'Pentalees'),(46,'Chhayalees'),(47,'Sentalees'),(48,'Adtalees'),(49,'Unchas') ,(50,'Pachaas'),(51,'Ikyawan'),(52,'Bawan'),(53,'Trepan'),(54,'Chauwan'),(55,'Pachpan'),(56,'Chhappan'),(57,'Sattawan') ,(58,'Atthawan'),(59,'Unsath'),(60,'Saath'),(61,'Iksath'),(62,'Basath'),(63,'Tresath'),(64,'Chausath'),(65,'Pensath') ,(66,'Chhiyasath'),(67,'Sarsath'),(68,'Adsath'),(69,'Unhattar'),(70,'Sattar'),(71,'Ikhattar'),(72,'Bahattar'),(73,'Tehattar') ,(74,'Chauhattar'),(75,'Pachhattar'),(76,'Chhiyattar'),(77,'Satattar'),(78,'Athattar'),(79,'Unyasi'),(80,'Assi'),(81,'Ikyasi') ,(82,'Bayasi'),(83,'Terasi'),(84,'Chaurasi'),(85,'Pachasi'),(86,'Chhiyasi'),(87,'Satasi'),(88,'Athasi'),(89,'Nawasi') ,(90,'Nabbe'),(91,'Inkyanwe'),(92,'Bannawe'),(93,'Terannwe'),(94,'Chaurannwe'),(95,'Pachannwe'),(96,'Chhiyannwe'),(97,'Sattannwe') ,(98,'Atthannvwe'),(99,'Ninnanwe')                  DECLARE @DigitDetails TABLE        (         DigitPlace   INT         ,PlaceName   VARCHAR(30)        ) INSERT INTO @DigitDetails  VALUES       (1,'Sau'),(2,'Hazar'),(4,'Crore'),(6,'Kharab'),(8,'Padma'),(10,'Ald')                  ,(12,'Jald'),(14,'Parardha'),(16,'Maha Ant'),(18,'Singhar'),(20,'Adant Singhar'),(3,'Lakh')                  ,(5,'Arab'),(7,'Neel'),(9,'Shankh'),(11,'Ank'),(13,'Madh'),(15,'Ant')                  ,(17,'Shisht'),(19,'Maha 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 N''+@StrFinal END

Function has been created.

Now I am using this function in my sql script.

Here I am using ‘AdventureWorksDW2012’ Database for this demo.

select pc.EnglishProductCategoryName As Product_Category, CAST(SUM(FIS.SalesAmount) AS INT) AS Total_Sales_Amount,dbo.udf_NumberToHindiWords (CAST(SUM(FIS.SalesAmount) AS INT) ) AS Total_Sales_Amount_In_Word, CAST(SUM(FIS.TaxAmt) AS INT) AS Total_TaxAmt,dbo.udf_NumberToHindiWords (CAST(SUM(FIS.TaxAmt)AS INT)) AS Total_TaxAmt_In_Word, CAST(SUM(FIS.Freight) AS INT) AS Total_Freight, dbo.udf_NumberToHindiWords (CAST(SUM(FIS.Freight)As INT)) AS Total_Freight_In_Word From FactInternetSales FIS Inner join DimProduct P on P.ProductKey=FIS.ProductKey inner join DimProductSubcategory psc on p.ProductSubcategoryKey=psc.ProductCategoryKey Inner Join DimProductCategory pc on pc.ProductCategoryKey=psc.ProductCategoryKey Group by pc.EnglishProductCategoryName UNION ALL Select  'Total', CAST(SUM(Total_SalesAmount) AS INT) AS Total_SalesAmount,dbo.udf_NumberToHindiWords (CAST(SUM(x.Total_SalesAmount)AS INT)) AS Total_Sales_Amount_In_Word, CAST(SUM(Total_TaxAmt)AS INT) AS Total_TaxAmt,dbo.udf_NumberToHindiWords (CAST(SUM(x.Total_TaxAmt) AS INT)) AS Total_TaxAmt_In_Word, CAST(SUM(Total_Freight)AS INT) AS Total_Freight,dbo.udf_NumberToHindiWords (CAST(SUM(x.Total_Freight)AS INT)) AS Total_Freight_In_Word FROM ( select pc.EnglishProductCategoryName As Product_Category, SUM(FIS.SalesAmount) AS Total_SalesAmount, SUM(FIS.TaxAmt) AS Total_TaxAmt, SUM(FIS.Freight) AS Total_Freight From FactInternetSales FIS Inner join DimProduct P on P.ProductKey=FIS.ProductKey inner join DimProductSubcategory psc on p.ProductSubcategoryKey=psc.ProductCategoryKey Inner Join DimProductCategory pc on pc.ProductCategoryKey=psc.ProductCategoryKey Group by pc.EnglishProductCategoryName )X

See the result 

Result in excel