Convert Number into Words in SQL Server

  In SQL Server 2005, 2008, 2012, In MS SQL, to Convert Number into English Words or Hindi Words, you need to write a function, because we do not have an inbuilt function which could do the job. To Convert in Hindi words, follow the link here.. Below is the script to create function to convert large numbers into the English words :

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   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,'Hundred'

   UNION ALL SELECT 2,'Thousand'       UNION ALL SELECT 3,'Million'

   UNION ALL SELECT 4,'Billion'        UNION ALL SELECT 5,'Trillion'

   UNION ALL SELECT 6,'Quadrillion'   UNION ALL SELECT 7,'Quintillion'

   UNION ALL SELECT 8,'Sextillion'    UNION ALL SELECT 9,'Septillion'

   UNION ALL SELECT 10,'Octillion'    UNION ALL SELECT 11,'Nonillion'

   UNION ALL SELECT 12,'Decillion'    UNION ALL SELECT 13,'UnDecillion'

   UNION ALL SELECT 14,'DuoDecillion' UNION ALL SELECT 15,'TreDecillion'

   UNION ALL SELECT 16,'QuattuorDecillion'

   UNION ALL SELECT 17,'QuinDecillion' UNION ALL SELECT 18,'SexDecillion'

   UNION ALL SELECT 19,'SepDecillion' UNION ALL SELECT 20,'OctoDecillion'

   UNION ALL SELECT 21,'NovemDecillion'UNION ALL SELECT 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)

                     ELSECHARINDEX('.', @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

   Check the output below :

SELECT dbo.[udf_NumberToEnglishWords]
(82000450612) AS RetValue

--
Output :

Eighty Two Billion,
Four Hundred Fifty Thousand, Six Hundred, Twelve

Reference: Govind Badkur(http://sqlserver20.blogspot.com)