question

AshokKumarBollineni avatar image
AshokKumarBollineni asked

How to convert number to words?

Hi All,

I need help to converting amount (number) to words using SQL Query.

Example: Rs. 287.06 -> TWO HUNDRED EIGHTY-SEVEN AND 06/100 RUPEES

Requesting you to suggest how can i achieve it.

Regards,

Ashok

sqlserver2012sqlteam
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jeff Moden avatar image
Jeff Moden answered

With the understanding that I wrote this a long time ago and it probably needs to be updated, the code that follows the example is what you need to build.

Here's the example usage...

 SELECT dbo.NumberToWords(287.06,'',' and ','/100 Rupees');


And here's the code for the objects you need to build.


--=============================================================================
--      Create and populate a Tally table
--=============================================================================
--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,0,1) AS N
   INTO dbo.Tally
   FROM sys.All_Columns ac1,
        sys.All_Columns ac2
;
--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Let the public use it
  GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
;
--=====================================================================================================================
--      Create the table that that is used to form the words.
--      This only needs to be executed once, ever.
--=====================================================================================================================
 CREATE TABLE dbo.NumberWord 
        (
        Unit          INT,
        UnitWord      VARCHAR(5),
        TeenWord      VARCHAR(9),
        TenWord       VARCHAR(7),
        ThousandWord  VARCHAR(11),
        CONSTRAINT PK_NumberWord_Unit 
        PRIMARY KEY CLUSTERED (UNIT) WITH FILLFACTOR = 100
        )
;
 INSERT INTO NumberWord WITH (TABLOCK)
        (Unit,UnitWord,TeenWord   ,TenWord  ,ThousandWord)
 SELECT 1    ,'One'   ,'Eleven'   ,'Ten'    ,'Thousand'    UNION ALL
 SELECT 2    ,'Two'   ,'Twelve'   ,'Twenty' ,'Million'     UNION ALL
 SELECT 3    ,'Three' ,'Thirteen' ,'Thirty' ,'Billion'     UNION ALL
 SELECT 4    ,'Four'  ,'Fourteen' ,'Fourty' ,'Trillion'    UNION ALL
 SELECT 5    ,'Five'  ,'Fifteen'  ,'Fifty'  ,'Quadrillion' UNION ALL
 SELECT 6    ,'Six'   ,'Sixteen'  ,'Sixty'  ,'Quintillion' UNION ALL
 SELECT 7    ,'Seven' ,'Seventeen','Seventy','Sextillion'  UNION ALL
 SELECT 8    ,'Eight' ,'Eighteen' ,'Eighty' ,'Septillion'  UNION ALL
 SELECT 9    ,'Nine'  ,'Nineteen' ,'Ninety' ,'Octillian'
;
GO
 CREATE FUNCTION dbo.NumberToWords 
/**************************************************************************************************
 Purpose:
 Convert a 0 to 2 decimal place number, including negative numbers, to words like you may find
 for the written out amount on a check.  "Optional extra" words may be added. See the "Input
 Parameters" section of the code below for more details.

 Notes:
 1. All parameters must be present but may be Blank (empty string) or Null

 Revision History:
 Rev 00 - 27 Nov 2008 - Jeff Moden - Initial creation for the folks at SQLServerCentral.com.
           References - Original request:
                        http://www.sqlservercentral.com/Forums/Topic584374-360-1.aspx

                        Original "final" code answer:
                        http://www.sqlservercentral.com/Forums/FindPost610007.aspx

                        Tally table info:
                        http://www.sqlservercentral.com/articles/TSQL/62867/

 Rev 01 - 29 Jun 2012 - Jeff Moden 
                      - Remove forced leading blanks from optional words for greater control.
                      - Modified for use with zero and unit based Tally Tables.
**************************************************************************************************/
--===== Input/Output parameters
        (
        @SomeNumber   DECIMAL(32,2),  -- +- 30 digits + up to 2 decimal places
        @IntegerType  VARCHAR(32),    -- Example: 'Dollar(s)' on checks or NULL or Blank
        @DecimalPlace VARCHAR(32),    -- Example: 'And' on checks or NULL or Blank
        @DecimalType  VARCHAR(32)     -- Example: 'Cent(s)' or '/100 Dollar(s)' on checks.
                                      --           Can also be NULL or Blank
        )
RETURNS VARCHAR(8000)
     AS
  BEGIN -------------------------------------------------------------------------------------------

--===== Declare local variables
DECLARE @Result       VARCHAR(8000), --Holds most of what we want to return
        @NumberString CHAR(33)       --The number converted to a right justified string
;
--===== Convert the number to a postive number and right justify it so we know where things are at.
 SELECT @NumberString = RIGHT(REPLICATE(' ',33) + CAST(ABS(@SomeNumber) AS VARCHAR(33)),33)
;
--===== Find and concatenate all the words we need for the positive integer part of the number
 SELECT @Result = COALESCE(@Result + ',' ,'')
      + ISNULL(' ' + hundred.UnitWord + ' Hundred','')
      + CASE WHEN split.Teen BETWEEN 11 AND 19 THEN '' ELSE ISNULL(' ' + ten.TenWord,'') END
      + CASE WHEN split.Teen BETWEEN 11 AND 19 THEN '' ELSE ISNULL(' ' + one.UnitWord,'') END
      + CASE WHEN split.Teen BETWEEN 11 AND 19 THEN ISNULL(' ' + teen.TeenWord,'') ELSE '' END
      + ISNULL(' ' + thousand.ThousandWord,'')
   FROM (--==== Split the number into component parts based on "triplets" of digits in the number.
             -- This is nothing more than a split using a Tally table in steps of 3 characters
             -- as controlled by Modulo 3.
         SELECT (28-N)/3                               AS Thousand,
                NULLIF(SUBSTRING(@NumberString,N,3),0) AS Triplet,
                SUBSTRING(@NumberString,N,1)           AS Hundred,
                SUBSTRING(@NumberString,N+1,1)         AS Ten,
                SUBSTRING(@NumberString,N+2,1)         AS One,
                SUBSTRING(@NumberString,N+1,2)         AS Teen
           FROM dbo.Tally
          WHERE N BETWEEN 1 AND 30
            AND N%3 = 1
        )split
   LEFT JOIN dbo.NumberWord AS hundred  ON split.Hundred       = hundred.Unit
   LEFT JOIN dbo.NumberWord AS ten      ON split.Ten           = ten.Unit
   LEFT JOIN dbo.NumberWord AS one      ON split.One           = one.Unit
   LEFT JOIN dbo.NumberWord AS teen     ON RIGHT(split.Teen,1) = teen.Unit
   LEFT JOIN dbo.NumberWord AS thousand ON split.Thousand      = thousand.Unit
  WHERE split.Triplet IS NOT NULL
;
--===== Add the other markers and parts to display and exit.
 RETURN CASE WHEN @SomeNumber < 0 THEN 'Minus ' ELSE '' END
      + ISNULL(LTRIM(@Result),'Zero') 
      + ISNULL(@IntegerType ,'')
      + ISNULL(@DecimalPlace ,'')
      + RIGHT(@NumberString,2)
      + ISNULL(@DecimalType ,'')
;
    END -------------------------------------------------------------------------------------------
;
GO
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Jeff Moden: Thank you so much for your valuable reply.

0 Likes 0 ·

@Jeff Moden: I have created tables and store procedure and function as per the provided document. i am getting out like below.

Rupees. AND 06/100 RUPEES like that. (Two Hundred Eighty seven is not diplaying.

Required is Example: Rs. 287.06 -> TWO HUNDRED EIGHTY-SEVEN AND 06/100 RUPEES


Requesting you suggest me do need to do any changes in function.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.