SQL Server - Procedure or function to convert decimal amount in words

Asked By kughan j on 09-Nov-11 07:41 AM
Hi,

Can anyone suggest me a procedure or function to convert the amount in figures to words which i need to use in my crystal report.
It should be applicable for both numeric and double datatype values

Thanks,
Kughan
Kirtan Patel replied to kughan j on 09-Nov-11 07:52 AM
CREATE FUNCTION dbo.udf_Num_ToWords (
 
    @Number Numeric (38, 0) -- Input number with as many as 18 digits
 
) RETURNS VARCHAR(8000)
 
AS BEGIN
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)
IF @Number = 0 Return 'Zero'
-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
   , @outputString = ''
   , @counter = 1
SELECT @length   = LEN(@inputNumber)
   , @position = LEN(@inputNumber) - 2
   , @loops    = LEN(@inputNumber)/3
 
-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
  UNION ALL SELECT '01', 'one'    UNION ALL SELECT '02', 'two'
  UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
  UNION ALL SELECT '05', 'five'   UNION ALL SELECT '06', 'six'
  UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
  UNION ALL SELECT '09', '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'   
 
WHILE @counter <= @loops BEGIN
 
    -- get chunks of 3 numbers at a time, padded with leading zeros
    SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
 
    IF @chunk <> '000' BEGIN
      SELECT @tensones = SUBSTRING(@chunk, 2, 2)
         , @hundreds = SUBSTRING(@chunk, 1, 1)
         , @tens = SUBSTRING(@chunk, 2, 1)
         , @ones = SUBSTRING(@chunk, 3, 1)
 
      -- If twenty or less, use the word directly from @NumbersTable
      IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
          SET @outputString = (SELECT word
                    FROM @NumbersTable
                    WHERE @tensones = number)
           + CASE @counter WHEN 1 THEN '' -- No name
             WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
             WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
             WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
             WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
             WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
             WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
             ELSE '' END
                 + @outputString
          END
       ELSE BEGIN -- break down the ones and the tens separately
 
       SET @outputString = ' '
              + (SELECT word
                  FROM @NumbersTable
                  WHERE @tens + '0' = number)
                     + '-'
               + (SELECT word
                  FROM @NumbersTable
                  WHERE '0'+ @ones = number)
           + CASE @counter WHEN 1 THEN '' -- No name
             WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
             WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
             WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
             WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
             WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
             WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
             ELSE '' END
              + @outputString
      END
 
      -- now get the hundreds
      IF @hundreds <> '0' BEGIN
          SET @outputString  = (SELECT word
                    FROM @NumbersTable
                    WHERE '0' + @hundreds = number)
                        + ' hundred '
                + @outputString
      END
    END
 
    SELECT @counter = @counter + 1
       , @position = @position - 3
END
-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)
RETURN @outputString -- return the result
END
GO
 
GRANT EXEC on dbo.udf_Num_ToWords TO PUBLIC
GO
 
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
dipa ahuja replied to kughan j on 09-Nov-11 07:58 AM
Create Procedure NumberToWord 
@Number AS BIGINT 
AS 
BEGIN 
DECLARE @Digits INT 
SET @Digits = @Number 
IF (LEN(@Digits) > 4)  
BEGIN 
RAISERROR ('PLEASE ENTER WITH IN 4 DIGIT NUMBERS',12,-1) 
END 
ELSE IF (LEN(@Digits) <= 4)  
BEGIN 
DECLARE @NumWords TABLE(Number INT, Ones VARCHAR(15),  Tens VARCHAR(15), Hundreds VARCHAR(15), Thousands VARCHAR(15)) 
INSERT INTO @NumWords 
SELECT 0,'','','','' UNION ALL 
 SELECT 1,'ONE','TEN','ONE HUNDRED','ONE THOUSAND' UNION ALL 
 SELECT 2,'TWO','TWENTY','TWO HUNDRED','TWO THOUSAND' UNION ALL 
 SELECT 3,'THREE','THIRTY','THREE HUNDRED','THREE THOUSAND' UNION ALL 
 SELECT 4,'FOUR','FORTY','FOUR HUNDRED','FOUR THOUSAND'  UNION ALL 
 SELECT 5,'FIVE','FIFTY','FIVE HUNDRED','FIVE THOUSAND'  UNION ALL 
 SELECT 6,'SIX','SIXTY','SIX HUNDRED','SIX THOUSAND'  UNION ALL 
 SELECT 7,'SEVEN','SEVENTY','SEVEN HUNDRED','SEVEN THOUSAND'  UNION ALL 
 SELECT 8,'EIGHT','EIGHTY','EIGHT HUNDRED','EIGHT THOUSAND' UNION ALL 
 SELECT 9,'NINE','NINETY','NINE HUNDRED','NINETHOUSAND' UNION ALL 
 SELECT 10,'NINETEEN','','','' 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','','',''  
   
   
SELECT RTRIM(MAX(CASE WHEN NUMBER = SUBSTRING(REVERSE(@Digits),4,1) THEN THOUSANDS ELSE '' END)) --FROM @NumWords  
    +SPACE(2)+ LTRIM(MAX(CASE WHEN NUMBER = SUBSTRING(REVERSE(@Digits),3,1) THEN HUNDREDS ELSE '' END) ) 
    +SPACE(2)+ MAX(CASE WHEN RIGHT(@Digits,2) NOT BETWEEN 11 AND 19 AND NUMBER = SUBSTRING(REVERSE(@Digits),2,1) THEN TENS ELSE '' END
    +SPACE(2)+ MAX(CASE WHEN RIGHT(@Digits,2) NOT BETWEEN 11 AND 19 AND NUMBER = RIGHT(@Digits,1) THEN ONES  
     WHEN RIGHT(@Digits,2) BETWEEN 11 AND 19 AND NUMBER = RIGHT(@Digits,2) THEN ONES ELSE '' END)  
     As "Number to Words"
FROM @NUMWORDS  
END 
END 
 
 
 
--EXEC NumberToWord 2010
Reena Jain replied to kughan j on 09-Nov-11 08:15 AM
hi,

Hi,

I found following script on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86067 which does this. Even for decimal points.

For example if you were to do

select dbo.Currency_ToWords(1235.34)
the output would be

ONE THOUSAND THIRTY FIVE DOLLARS AND 34/100 CENTS

I figured I'd post this, because I spent 2 hours trying to get it, and could not find a function like this for sql anywhere. enjoy!



Create FUNCTION [dbo].[Currency_ToWords] (
@Input Numeric (38, 3) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000)

/*
* Converts a integer number as large as 34 digits into the
* equivalent words.  The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
*      as revised by Nick Barclay
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
    +  dbo.udf_Num_ToWords (0) + CHAR(10)
    +  dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)

DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN
    PRINT convert (char(5), @i) 
        + convert(varchar(255), dbo.udf_Num_ToWords(@i))
    SET @I  = @i + 1
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN
Declare @Number Numeric(38,0)
set @Number = @Input
Declare @Cents as int
set @Cents = 100*Convert(money,(@Input - convert(Numeric(38,3),@Number)))
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
   , @outputString = ''
   , @counter = 1
SELECT @length   = LEN(@inputNumber)
   , @position = LEN(@inputNumber) - 2
   , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'    UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'   UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', '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'  

WHILE @counter <= @loops BEGIN

-- get chunks of 3 numbers at a time, padded with leading zeros
SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

IF @chunk <> '000' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
   , @hundreds = SUBSTRING(@chunk, 1, 1)
   , @tens = SUBSTRING(@chunk, 2, 1)
   , @ones = SUBSTRING(@chunk, 3, 1)

-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
                    FROM @NumbersTable
                    WHERE @tensones = number)
           + CASE @counter WHEN 1 THEN '' -- No name
             WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
             WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
             WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
             WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
             WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
             WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
             ELSE '' END
                 + @outputString
    END
ELSE BEGIN -- break down the ones and the tens separately

       SET @outputString = ' '
                + (SELECT word
                    FROM @NumbersTable
                    WHERE @tens + '0' = number)
     + '-'
               + (SELECT word
                    FROM @NumbersTable
                    WHERE '0'+ @ones = number)
           + CASE @counter WHEN 1 THEN '' -- No name
             WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
             WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
             WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
             WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
             WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
             WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
             ELSE '' END
                + @outputString
END

-- now get the hundreds
IF @hundreds <> '0' BEGIN
SET @outputString  = (SELECT word
                    FROM @NumbersTable
                    WHERE '0' + @hundreds = number)
        + ' hundred '
                  + @outputString
END
END

SELECT @counter = @counter + 1
   , @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN UPPER(@outputString) + ' DOLLARS & ' +convert(Varchar(20),@Cents) + '/100 CENTS'-- return the result
END


try this and let me know
aneesa replied to kughan j on 09-Nov-11 12:53 PM

-- Convert numbers to words - SQL amount into words - Currency to words

-- SQL money format to English - Translate money to text

-- Translate dollar amount to words - Convert numbers into English words

USE AdventureWorks2008;

GO

-- Convert numbers to text - Scalar-valued user-defined function - UDF

CREATE FUNCTION fnMoneyToEnglish(@Money AS money)

    RETURNS VARCHAR(1024)

AS

BEGIN

    DECLARE @Number as BIGINT

    SET @Number = FLOOR(@Money)

    DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

    DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

    INSERT @Below20 (Word) VALUES

              ( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

              ( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

              ( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

              ( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

              ( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

              ('Eighteen' ), ( 'Nineteen' )

     INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

                 ('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @English varchar(1024) =

(

  SELECT Case

    WHEN @Number = 0 THEN  ''

    WHEN @Number BETWEEN 1 AND 19

    THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

   WHEN @Number BETWEEN 20 AND 99

-- SQL Server recursive function   

   THEN  (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

       dbo.fnMoneyToEnglish( @Number % 10)

   WHEN @Number BETWEEN 100 AND 999  

   THEN  (dbo.fnMoneyToEnglish( @Number / 100))+' Hundred '+

     dbo.fnMoneyToEnglish( @Number % 100)

   WHEN @Number BETWEEN 1000 AND 999999  

   THEN  (dbo.fnMoneyToEnglish( @Number / 1000))+' Thousand '+

     dbo.fnMoneyToEnglish( @Number % 1000) 

   WHEN @Number BETWEEN 1000000 AND 999999999  

   THEN  (dbo.fnMoneyToEnglish( @Number / 1000000))+' Million '+

     dbo.fnMoneyToEnglish( @Number % 1000000)

   ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

         WHERE RIGHT(@English,1)='-'

IF @@NestLevel = 1

BEGIN

    SELECT @English = @English+' Dollars and '

    SELECT @English = @English+

    convert(varchar,convert(int,100*(@Money - @Number))) +' Cents'

END

RETURN (@English)

END

GO

-- Test number to English dollar and cents translation function

-- SQL convert number to text

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 67)

-- Sixty-Seven Dollars and 0 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 947.54)

-- Nine Hundred Forty-Seven Dollars and 54 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 1266.04)

-- One Thousand Two Hundred Sixty-Six Dollars and 4 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 4261.25)

-- Four Thousand Two Hundred Sixty-One Dollars and 25 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 921456321.88)

/* Nine Hundred Twenty-One Million Four Hundred Fifty-Six Thousand

Three Hundred Twenty-One Dollars and 88 Cents */

GO

------------