Let's say we have given a number 145.49. We have to convert the number in words such that, the desire output will be One Hundred Fourty Five and Fourty Nine. In this article, we will look into the conversion of the same using TSQL in SET Based Way.
Introduction
Let's say we have given a number 145.49. We have to convert the number in words such that, the desire output will be One Hundred Fourty Five and Fourty Nine. In this article, we will look into the conversion of the same using TSQL in SET Based Way.
In an earlier article we have seen how to Convert Number To Words using TSQL in SET Based Way. This article is an extension to the same.
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: Divide the original number into Real and Decimal Parts
Step 2: Generate Number to Words between 1 to 99.
Step 3: Divide the number into their digits
Step 4: Position the result of Step 2 according to the unitary system.
Step 5: Combine the Result of Step 1 and 3 to generate number to words for Real Part.
Step 6: Repeat Step 5 for Decimal Parts
Step by step solution of the algorithm
Step 1: Divide the original number into Real and Decimal Parts.
DECLARE @Number VARCHAR(20) = '145.49'
--Step 1:Divide the original number into Real and Decimal Parts
DECLARE @RealPart INT = PARSENAME(@Number, 2)
DECLARE @DecimalPart INT = PARSENAME(@Number, 1)
Step 2: Generate Number to Words between 1 to 99.
-- Step 2: 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 3: Divide the number into their digits
-- Step 3: Divide the number into their digits
;WITH getDigitsCTE AS (
SELECT
UnitarySystemPosition=1
,Quotient = @RealPart / 10
,Remainder = @RealPart % 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 4: 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 5: 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
,digitWordsCombinationForRealPartCTE 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 digitWordsCombinationForRealPartCTE
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 =
--Real Part
STUFF((SELECT '' +
dw.NumberToWords + ' '
FROM digitWordsCombinationForRealPartCTE dw
ORDER BY dw.SlNo
FOR XML PATH('')),1,0,'')
--Decimal Part
+
'and '
+
( SELECT
n.NumberToWords
FROM numberTableFrom1to99CTE n
WHERE n.Digit = @DecimalPart
)
Result
---------
OriginalNumber InWords
------------- -------
145.49 One Hundred Fourty Five and Fourty Nine
The program can be easily extended to accomodate 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 VARCHAR(20) = '987654321.12'
--Step 1:Divide the original number into Real and Decimal Parts
DECLARE @RealPart INT = PARSENAME(@Number, 2)
DECLARE @DecimalPart INT = PARSENAME(@Number, 1)
-- Step 2: 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 3: Divide the number into their digits
, getDigitsCTE AS (
SELECT
UnitarySystemPosition=1
,Quotient = @RealPart / 10
,Remainder = @RealPart % 10
UNION ALL
SELECT
UnitarySystemPosition=UnitarySystemPosition+1
,Quotient / 10
, Quotient % 10
FROM getDigitsCTE
WHERE Quotient > 0
)
-- Step 4: Position the result of Step 3 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 5: 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 6: Combine the Result of Step 1 and 5 to generate number to words for Real Part
,digitWordsCombinationForRealPartCTE 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 =
--Real Part
STUFF((SELECT '' +
dw.NumberToWords + ' '
FROM digitWordsCombinationForRealPartCTE dw
ORDER BY dw.SlNo
FOR XML PATH('')),1,0,'')
--Decimal Part
+
'and '
+
( SELECT
n.NumberToWords
FROM numberTableFrom1to99CTE n
WHERE n.Digit = @DecimalPart
)
/*
OriginalNumber InWords
------------- -------
987654321.12 Ninety Eight Crore Seventy Six Lac Fifty Four Thousand Three Hundred Twenty One and Twelve
*/
Conclusion
Hope this article will help us to write better logic and program in TSQL. Thanks for reading. Zipped file attached.