# question

## 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

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

·

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
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

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```

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

·

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 ·