Convert Number To Words using TSQL in SET Based Way

Rajnilari2015
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 3007 red flag

Let's say we have given a number 145. We have to convert the number in words such that, the desire output will be One Hundred Thrirty Four. In this article, we will look into the conversion of the same using TSQL in SET Based Way.


 Download source code for Convert Number To Words using TSQL in SET Based Way

Dedicated this article to my wife(Arina) on our 7th Valentine's Day

Introduction

Let's say we have given a number 145. We have to convert the number in words such that, the desire output will be One Hundred Fourty Five. In this article, we will look into the conversion of the same using TSQL in SET Based Way.

Approach to solve

In unitary system we have(diagram representing till 99 Crores i.e. 9 digits)

Representing the above value in the unitary system, we get

This will helps us to identify the position of the digits in the unitary system and based on this taxonomy we can easily transform the digits to words.

Now, based on the above concept we will write our algorithm

Algorithm

Step 1: Generate Number to Words between 1 to 99.

Step 2: Divide the number into their digits

Step 3: Position the result of Step 2 according to the unitary system.

Step 4: Combine the Result of Step 1 and 3 to generate number to words.

Step by step solution of the algorithm

Step 1: Generate Number to Words between 1 to 99.

-- Step 1: Generate Number to Words between 1 to 99

--Generate Numbers From 1 to 19
;WITH generateNumbersFrom1To19CTE AS(
SELECT 1 AS Digit , 'One' AS NumberToWords 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' )

--Generate Numbers From 20 to 99
,generateNumbersFrom20To99 AS(

	SELECT Rn=20
	UNION ALL
	SELECT Rn=Rn+1
	FROM GenerateNumbersFrom20To99 WHERE Rn<99)

-- Generate Numbers between 1 to 99
,numberTableFrom1to99CTE AS(

SELECT * FROM generateNumbersFrom1To19CTE
UNION ALL

SELECT 
	Rn
	,NumberToWords=

			IIF(Rn/10 = 2,'Twenty '
			,IIF(Rn/10 = 3,'Thirty '
			,IIF(Rn/10 = 4,'Fourty '
			,IIF(Rn/10 = 5,'Fifty '
			,IIF(Rn/10 = 6,'Sixty '
			,IIF(Rn/10 = 7,'Seventy '
			,IIF(Rn/10 = 8,'Eighty '
			,IIF(Rn/10 = 9,'Ninety ',''))))))))  +
			IIF(Rn%10 = 1,'One'
			,IIF(Rn%10 = 2,'Two'
			,IIF(Rn%10 = 3,'Three'
			,IIF(Rn%10 = 4,'Four'
			,IIF(Rn%10 = 5,'Five'
			,IIF(Rn%10 = 6,'Six'
			,IIF(Rn%10 = 7,'Seven'
			,IIF(Rn%10 = 8,'Eight'
			,IIF(Rn%10 = 9,'Nine','')))))))))
FROM GenerateNumbersFrom20To99)

SELECT 
	*
FROM numberTableFrom1to99CTE

First we have generate the numbers and their corresponding words between 1 to 19 by using the generateNumbersFrom1To19CTE CTE and then between 20 to 99 by using GenerateNumbersFrom20To99 recursive CTE and finally combined them inside the numberTableFrom1to99CTE CTE.

The result at this stage

Digit	NumberToWords
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 
21	Twenty One
22	Twenty Two
23	Twenty Three
24	Twenty Four
25	Twenty Five
26	Twenty Six
27	Twenty Seven
28	Twenty Eight
29	Twenty Nine
30	Thirty 
31	Thirty One
32	Thirty Two
33	Thirty Three
34	Thirty Four
35	Thirty Five
36	Thirty Six
37	Thirty Seven
38	Thirty Eight
39	Thirty Nine
40	Fourty 
41	Fourty One
42	Fourty Two
43	Fourty Three
44	Fourty Four
45	Fourty Five
46	Fourty Six
47	Fourty Seven
48	Fourty Eight
49	Fourty Nine
50	Fifty 
51	Fifty One
52	Fifty Two
53	Fifty Three
54	Fifty Four
55	Fifty Five
56	Fifty Six
57	Fifty Seven
58	Fifty Eight
59	Fifty Nine
60	Sixty 
61	Sixty One
62	Sixty Two
63	Sixty Three
64	Sixty Four
65	Sixty Five
66	Sixty Six
67	Sixty Seven
68	Sixty Eight
69	Sixty Nine
70	Seventy 
71	Seventy One
72	Seventy Two
73	Seventy Three
74	Seventy Four
75	Seventy Five
76	Seventy Six
77	Seventy Seven
78	Seventy Eight
79	Seventy Nine
80	Eighty 
81	Eighty One
82	Eighty Two
83	Eighty Three
84	Eighty Four
85	Eighty Five
86	Eighty Six
87	Eighty Seven
88	Eighty Eight
89	Eighty Nine
90	Ninety 
91	Ninety One
92	Ninety Two
93	Ninety Three
94	Ninety Four
95	Ninety Five
96	Ninety Six
97	Ninety Seven
98	Ninety Eight
99	Ninety Nine

Step 2: Divide the number into their digits

-- Step 2: Divide the number into their digits
DECLARE @Number INT = 145

;WITH getDigitsCTE AS (
    SELECT 
			UnitarySystemPosition=1
			,Quotient = @Number / 10 
			,Remainder = @Number % 10  

    UNION ALL

    SELECT 
			UnitarySystemPosition=UnitarySystemPosition+1
			,Quotient / 10
			, Quotient % 10

    FROM getDigitsCTE

    WHERE Quotient > 0
)

SELECT 
	UnitarySystemPosition
	,IndividualDigits = Remainder
FROM getDigitsCTE
ORDER BY 2

Result
---------
UnitarySystemPosition	IndividualDigits
3	1
2	4
1	5

It can be understand better in the below figure

Step 3: Position the result of Step 2 according to the unitary system.

,transformDigitsIntoUnitarySystem AS(
SELECT
	Hundred = 
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
	WHERE UnitarySystemPosition IN(3)
	ORDER BY dw.UnitarySystemPosition DESC
	FOR XML PATH('')),1,0,'')

	,TensAndUnit = 
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
	WHERE UnitarySystemPosition IN(1,2)
	ORDER BY dw.UnitarySystemPosition DESC
	FOR XML PATH('')),1,0,''))

SELECT *
FROM transformDigitsIntoUnitarySystem

As can be figure out that, the UNIT and TENth digits apprered in the position 1 and 2 while the HUNDREDth at potition 3. Henceforth, inside the subqueries we have piked up the numbers as per that. And by using the FOR XML PATH we have transformed the rows to columns inside the transformDigitsIntoUnitarySystem CTE.

The result is as under

Hundred	TensAndUnit
1	45

Now label the numbers into the unitary system by using UNPIVOT as under

,labelNumbersInUnitarySystemCTE AS(
SELECT  
	SlNo=ROW_NUMBER() OVER(ORDER BY (SELECT 1))
	, UnitarySystem
	, Numbers
FROM 
   (SELECT   Hundred, TensAndUnit
   FROM transformDigitsIntoUnitarySystem) p
UNPIVOT
   (Numbers FOR UnitarySystem IN 
      ( Hundred, TensAndUnit)
)AS unpvt)

SELECT *
FROM labelNumbersInUnitarySystemCTE

Result
---------
SlNo	UnitarySystem	Numbers
1	Hundred		1
2	TensAndUnit	45

Step 4: Combine the Result of Step 1 and 3 to generate number to words.

--Step 4: Combine the Result of Step 1 and 3 to generate number to words
,digitWordsCombinationCTE AS(
SELECT 
		sd.*
		,NumberToWords=nd.NumberToWords + ' ' + IIF(sd.UnitarySystem = 'TensAndUnit','',sd.UnitarySystem)
FROM labelNumbersInUnitarySystemCTE sd
JOIN numberTableFrom1to99CTE nd ON nd.Digit = sd.Numbers)

SELECT *
FROM digitWordsCombinationCTE

Result
----------
SlNo	UnitarySystem	Numbers	NumberToWords
1	Hundred		1	One Hundred
2	TensAndUnit	45	Fourty Five 

As as final step we have to transform the result into one column which we will do by using FOR XML PATH

SELECT 
	OriginalNumber=@Number
	,InWords = 
	STUFF((SELECT '' + 
	dw.NumberToWords + ' ' 
FROM digitWordsCombinationCTE dw
ORDER BY dw.SlNo 
FOR XML PATH('')),1,0,'')

Result
---------
OriginalNumber	InWords
145		One Hundred Fourty Five  

The program can be easily extended to accommodate a huge number digit as shown under

Changes 1 - in the transformDigitsIntoUnitarySystem CTE extend the units like Thousands, Lacs, Crores etc.

-- Step 3: Position the result of Step 2 according to the unitary system.
,transformDigitsIntoUnitarySystem AS(
SELECT

	Crore=
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
		WHERE UnitarySystemPosition IN(8,9)
		ORDER BY dw.UnitarySystemPosition DESC
		FOR XML PATH('')),1,0,'')

	,Lac=
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
		WHERE UnitarySystemPosition IN(6,7)
		ORDER BY dw.UnitarySystemPosition DESC
		FOR XML PATH('')),1,0,'')
	,Thousand = 
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
	WHERE UnitarySystemPosition IN(4,5)
	ORDER BY dw.UnitarySystemPosition DESC
	FOR XML PATH('')),1,0,'')

	,Hundred = 
		...............................................
		...............................................

Changes 2 - in the labelNumbersInUnitarySystemCTE CTE, those column should participate in the UNPIVOT as under

,labelNumbersInUnitarySystemCTE AS(
SELECT  
	SlNo=ROW_NUMBER() OVER(ORDER BY (SELECT 1))
	, UnitarySystem
	, Numbers
FROM 
   (SELECT   Crore,Lac,Thousand,Hundred, TensAndUnit
   FROM transformDigitsIntoUnitarySystem) p
UNPIVOT
   (Numbers FOR UnitarySystem IN 
      ( Crore,Lac,Thousand,Hundred, TensAndUnit)
)AS unpvt)

Complete TSQL Script

DECLARE @Number INT = 345237819

-- Step 1: Generate Number to Words between 1 to 99

--Generate Numbers From 1 to 19
;WITH generateNumbersFrom1To19CTE AS(
SELECT 1 AS Digit , 'One' AS NumberToWords 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' )

--Generate Numbers From 20 to 99
,generateNumbersFrom20To99 AS(

	SELECT Rn=20
	UNION ALL
	SELECT Rn=Rn+1
	FROM GenerateNumbersFrom20To99 WHERE Rn<99)

-- Generate Numbers between 1 to 99
,numberTableFrom1to99CTE AS(

SELECT * FROM generateNumbersFrom1To19CTE
UNION ALL

SELECT 
	Rn
	,NumberToWords=

			IIF(Rn/10 = 2,'Twenty '
			,IIF(Rn/10 = 3,'Thirty '
			,IIF(Rn/10 = 4,'Fourty '
			,IIF(Rn/10 = 5,'Fifty '
			,IIF(Rn/10 = 6,'Sixty '
			,IIF(Rn/10 = 7,'Seventy '
			,IIF(Rn/10 = 8,'Eighty '
			,IIF(Rn/10 = 9,'Ninety ',''))))))))  +
			IIF(Rn%10 = 1,'One'
			,IIF(Rn%10 = 2,'Two'
			,IIF(Rn%10 = 3,'Three'
			,IIF(Rn%10 = 4,'Four'
			,IIF(Rn%10 = 5,'Five'
			,IIF(Rn%10 = 6,'Six'
			,IIF(Rn%10 = 7,'Seven'
			,IIF(Rn%10 = 8,'Eight'
			,IIF(Rn%10 = 9,'Nine','')))))))))
FROM GenerateNumbersFrom20To99)

-- Step 2: Divide the number into their digits
, getDigitsCTE AS (
    SELECT 
			UnitarySystemPosition=1
			,Quotient = @Number / 10 
			,Remainder = @Number % 10  

    UNION ALL

    SELECT 
			UnitarySystemPosition=UnitarySystemPosition+1
			,Quotient / 10
			, Quotient % 10

    FROM getDigitsCTE

    WHERE Quotient > 0
)

-- Step 3: Position the result of Step 2 according to the unitary system.
,transformDigitsIntoUnitarySystem AS(
SELECT

	Crore=
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
		WHERE UnitarySystemPosition IN(8,9)
		ORDER BY dw.UnitarySystemPosition DESC
		FOR XML PATH('')),1,0,'')

	,Lac=
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
		WHERE UnitarySystemPosition IN(6,7)
		ORDER BY dw.UnitarySystemPosition DESC
		FOR XML PATH('')),1,0,'')
	,Thousand = 
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
	WHERE UnitarySystemPosition IN(4,5)
	ORDER BY dw.UnitarySystemPosition DESC
	FOR XML PATH('')),1,0,'')

	,Hundred = 
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
	WHERE UnitarySystemPosition IN(3)
	ORDER BY dw.UnitarySystemPosition DESC
	FOR XML PATH('')),1,0,'')

	,TensAndUnit = 
		STUFF((SELECT '' + 
		dw.Remainder + ' ' 
		FROM getDigitsCTE dw
	WHERE UnitarySystemPosition IN(1,2)
	ORDER BY dw.UnitarySystemPosition DESC
	FOR XML PATH('')),1,0,''))

-- Step 4: Label the numbers into the unitary system
,labelNumbersInUnitarySystemCTE AS(
SELECT  
	SlNo=ROW_NUMBER() OVER(ORDER BY (SELECT 1))
	, UnitarySystem
	, Numbers
FROM 
   (SELECT   Crore,Lac,Thousand,Hundred, TensAndUnit
   FROM transformDigitsIntoUnitarySystem) p
UNPIVOT
   (Numbers FOR UnitarySystem IN 
      ( Crore,Lac,Thousand,Hundred, TensAndUnit)
)AS unpvt)

--Step 5: Combine the Result of Step 1 and 4 to generate number to words
,digitWordsCombinationCTE AS(
SELECT 
		sd.*
		,NumberToWords=nd.NumberToWords + ' ' + IIF(sd.UnitarySystem = 'TensAndUnit','',sd.UnitarySystem)
FROM labelNumbersInUnitarySystemCTE sd
JOIN numberTableFrom1to99CTE nd ON nd.Digit = sd.Numbers)

SELECT 
	OriginalNumber=@Number
	,InWords = 
	STUFF((SELECT '' + 
	dw.NumberToWords + ' ' 
FROM digitWordsCombinationCTE dw
ORDER BY dw.SlNo 
FOR XML PATH('')),1,0,'')


Result
---------
OriginalNumber	InWords
345237819	Thirty Four Crore Fifty Two Lac Thirty Seven Thousand Eight Hundred Nineteen  

Conclusion

Hope this article will help us to write better logic and program in TSQL. This program can be easily re-usable as a function. It can even be extended to accommodate decimal places. The article has taught us at the bare minimum -

  1. Usage of Recursive CTE
  2. Usage of For XML Path
  3. Usage of Stuff Function
  4. Applicability of SubQuery
  5. Usage of UnPivot
  6. Generation of Serial Number on the fly using ROW_NUMBER() function
  7. Usage of IIF
  8. and many more...

Thanks for reading. Zipped file attached.

Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,Microsoft_MVP,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Posted by: Sheonarayan on: 2/14/2017 | Points: 25
Thanks Niladri,

Please convey my best wishes to Arian. Good going, keep it up!

Best regards
Posted by: Rajnilari2015 on: 2/15/2017 | Points: 25
Thanks you Sir and sure I will convey the same to her (:

Login to post response

Comment using Facebook(Author doesn't get notification)