# Convert Decimal Number To Words using TSQL in SET Based Way

Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 4632

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.

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

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.