Convert Number into 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

CREATE FUNCTION [dbo].[udf_NumberToEnglishWords] ( @InNumericValue      NUMERIC(38,2) ) RETURNS VARCHAR(1000) AS BEGIN DECLARE @NumericDetails TABLE        (         Number       INT        ,Word         VARCHAR(20)        ) INSERT INTO @NumericDetails VALUES (1,'One'),(2,'Two'),( 3,'Three'),(4,'Four'),( 5,'Five'), (6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine'),(10,'Ten'), (11,'Eleven'),(12,'Twelve'),(13,'Thirteen'),(14,'Fourteen'), (15,'Fifteen'),(16,'Sixteen'),(17,'Seventeen'),(18,'Eighteen'), (19,'Nineteen'),(20,'Twenty'),(30,'Thirty'),(40,'Forty'), (50,'Fifty'),(60,'Sixty'),(70,'Seventy'),(80,'Eighty'),(90,'Ninety')        DECLARE @DigitDetails TABLE        (           DigitPlace   INT          ,PlaceName    VARCHAR(30)        ) INSERT INTO @DigitDetails VALUES (1,'Hundred'),(2,'Thousand'),(4,'Billion'),(6,'Quadrillion')   ,(8,'Sextillion'),(10,'Octillion'),(12,'Decillion'),(14,'DuoDecillion') ,(16,'QuattuorDecillion'),(17,'QuinDecillion'),(19,'SepDecillion'),(21,'NovemDecillion') ,(3,'Million'),(5,'Trillion'),(7,'Quintillion'),(9,'Septillion') ,(11,'Nonillion'),(13,'UnDecillion'),(15,'TreDecillion'),(18,'SexDecillion') ,(20,'OctoDecillion'),(22,'Vigintillion') DECLARE       @StrNumber          VARCHAR(60)               ,@LargeNumber        VARCHAR(60)               ,@SmallNumber        VARCHAR(10)               ,@DecimalNumber      VARCHAR(10)               ,@Chunk              VARCHAR(50)               ,@ChunkVal           INT               ,@TenthVal           INT               ,@UnitVal            INT               ,@HundVal            INT               ,@StrFinal           VARCHAR(1000)               ,@StrLength          INT               ,@DigitLoop          INT SET @DecimalNumber = '' SET @HundVal = 0 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 @StrFinal = RIGHT(@StrNumber,3)     SET @LargeNumber = SUBSTRING(@StrNumber,-2,@StrLength)     SET @StrLength  = LEN(@StrNumber)     WHILE LEN(@LargeNumber) > 0     BEGIN         SET @Chunk = ''  SET @HundVal = 0         SET @ChunkVal = CAST(RIGHT(@LargeNumber,3) AS INT)        IF (@ChunkVal >= 100)        BEGIN            SET @HundVal = @ChunkVal/100  -- SET @HundVal = CAST(LEFT(@LargeNumber,1) AS INT)            SELECT @Chunk = WORD FROM @NumericDetails            WHERE NUMBER = @HundVal            SET @Chunk = @Chunk + ' ' + 'Hundred'  -- SET @StrFinal = @Chunk + @StrFinal            SET @ChunkVal = @ChunkVal%100        END         IF (@ChunkVal > 20)         BEGIN             SET @TenthVal = (@ChunkVal/10)*10             SET @UnitVal = @ChunkVal%10             SELECT @Chunk=@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,-2,@StrLength)             SET @DigitLoop = @DigitLoop + 1         END         ELSE         BEGIN             SELECT @Chunk=@Chunk +' '+ WORD FROM @NumericDetails                 WHERE NUMBER = @ChunkVal             SELECT @Chunk=@Chunk+' '+PlaceName FROM @DigitDetails                 WHERE DigitPlace = @DigitLoop             IF(@ChunkVal > 0 OR @HundVal > 0)             SET @StrFinal = @Chunk + ', ' + @StrFinal             SET @StrLength =LEN(@LargeNumber)             SET @LargeNumber=SUBSTRING(@LargeNumber,-2,@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+' '+'Hundred' 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

Function has been created.

Now I am using this function in my sql script.

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

USE [AdventureWorksDW2012] GO select pc.EnglishProductCategoryName As Product_Category, SUM(FIS.SalesAmount) AS Total_Sales_Amount, dbo.udf_NumberToEnglishWords (SUM(FIS.SalesAmount)) AS Total_Sales_Amount_In_Word, SUM(FIS.TaxAmt) AS Total_TaxAmt, dbo.udf_NumberToEnglishWords (SUM(FIS.TaxAmt)) AS Total_TaxAmt_In_Word, SUM(FIS.Freight) AS Total_Freight, dbo.udf_NumberToEnglishWords (SUM(FIS.Freight)) 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', SUM(Total_SalesAmount) AS Total_SalesAmount, dbo.udf_NumberToEnglishWords (SUM(x.Total_SalesAmount)) AS Total_Sales_Amount_In_Word, SUM(Total_TaxAmt) AS Total_TaxAmt, dbo.udf_NumberToEnglishWords (SUM(x.Total_TaxAmt)) AS Total_TaxAmt_In_Word, SUM(Total_Freight) AS Total_Freight, dbo.udf_NumberToEnglishWords (SUM(x.Total_Freight)) 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 output

  

Result in Excel

  

Hope this helps you for the reporting.