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.
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 -
- Usage of Recursive CTE
- Usage of For XML Path
- Usage of Stuff Function
- Applicability of SubQuery
- Usage of UnPivot
- Generation of Serial Number on the fly using ROW_NUMBER() function
- Usage of IIF
- and many more...
Thanks for reading. Zipped file attached.