Convert Words To Decimal Number using TSQL in SET Based Way

Posted by Niladri Biswas (RNA Team) in Sql Server category on 2/15/2017 for Intermediate level | Points: 250 | Views : 2304

Post Article |

Search Articles |

Articles Home

Let's say we have One Hundred Thirty Four and Nineteen. We have to convert the words in Decimal Number such that, the desire output will be 134.19. In this article, we will look into the conversion of the same using TSQL in SET Based Way.

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

Introduction

Let's say we have One Hundred Thirty Four and Nineteen. We have to convert the words in Decimal Number such that, the desire output will be 134.19. In this article, we will look into the conversion of the same using TSQL in SET Based Way. This article is an extension to the article Convert Words To Number using TSQL in SET Based Way.

Step by step approach to solve

Step 1: Divide the original number into Real and Decimal Parts.

DECLARE @words VARCHAR(100) = 'One Hundred Thirty Four and Nineteen'

--Step 1:Divide the original number into Real and Decimal Parts
DECLARE @RealPart VARCHAR(1000) = PARSENAME(REPLACE(@words,' and ','.'),2)
DECLARE @DecimalPart VARCHAR(1000) = PARSENAME(REPLACE(@words,' and ','.'), 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: Split the words based on their unitary system position

-- Step 3: Split the words based on their unitary system position

,splitWordsAndPositionByUNITARYSystemCTE AS(
        SELECT  
                UnitarySystemPosition=ROW_NUMBER() Over(Order by (Select 1))
                ,SplittedWords = REPLACE(value,' ','')
                FROM STRING_SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(@RealPart, 'Crore', '.'),'Lac','.'),'Thousand','.'),'Hundred','.'), '.')
)

SELECT *
FROM splitWordsAndPositionByUNITARYSystemCTE

Result
---------
UnitarySystemPosition	SplittedWords
1			One
2			ThirtyFour

Step 4: Combine the Result of Step 2 and 3 to generate words to number(s).

--Step 4: Combine the Result of Step 2 and 3 to generate words to number(s)

,digitWordsCombinationCTE AS(
	SELECT 
		swUS.UnitarySystemPosition
		,swUS.SplittedWords	
		,nT.Digit		
	FROM splitWordsAndPositionByUNITARYSystemCTE swUS
	JOIN numberTableFrom1to99CTE nT ON swUS.SplittedWords=REPLACE(nT.NumberToWords,' ',''))

SELECT *
FROM digitWordsCombinationCTE

Result
----------
UnitarySystemPosition	SplittedWords	Digit
1			One		1
2			ThirtyFour	34

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

--Final Projection
SELECT 
    OriginalWord=@words
    ,InDecimalNumber = 
			STUFF((SELECT '' + 
					dw.Digit + ' ' 
			FROM digitWordsCombinationCTE dw
			ORDER BY dw.UnitarySystemPosition 
			FOR XML PATH('')),1,0,'')
			+
			'.'
			+
			CAST((
				SELECT
					n.Digit
			FROM numberTableFrom1to99CTE n
			WHERE n.NumberToWords = @DecimalPart
			) AS VARCHAR(2))

Result
---------
OriginalWord				InDecimalNumber
------------				---------------
One Hundred Thirty Four and Nineteen	134.19

Complete TSQL Script

DECLARE @words VARCHAR(100) = 'Ninety Eight Crore Seventy Six Lac Fifty Four Thousand Three Hundred Twenty One and Twelve'

--Step 1:Divide the original number into Real and Decimal Parts
DECLARE @RealPart VARCHAR(1000) = PARSENAME(REPLACE(@words,' and ','.'),2)
DECLARE @DecimalPart VARCHAR(1000) = PARSENAME(REPLACE(@words,' and ','.'), 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: Split the words based on their unitary system position
,splitWordsAndPositionByUNITARYSystemCTE AS(
        SELECT  
                UnitarySystemPosition=ROW_NUMBER() Over(Order by (Select 1))
                ,SplittedWords = REPLACE(value,' ','')
                FROM STRING_SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(@RealPart, 'Crore', '.'),'Lac','.'),'Thousand','.'),'Hundred','.'), '.')
)

--Step 4: Combine the Result of Step 2 and 3 to generate words to number(s)
,digitWordsCombinationCTE AS(
        SELECT 
                swUS.UnitarySystemPosition
                ,swUS.SplittedWords     
                ,nT.Digit               
        FROM splitWordsAndPositionByUNITARYSystemCTE swUS
        JOIN numberTableFrom1to99CTE nT ON swUS.SplittedWords=REPLACE(nT.NumberToWords,' ',''))

--Final Projection
SELECT 
    OriginalWord=@words
    ,InDecimalNumber = 
			STUFF((SELECT '' + 
					dw.Digit + ' ' 
			FROM digitWordsCombinationCTE dw
			ORDER BY dw.UnitarySystemPosition 
			FOR XML PATH('')),1,0,'')
			+
			'.'
			+
			CAST((
				SELECT
					n.Digit
			FROM numberTableFrom1to99CTE n
			WHERE n.NumberToWords = @DecimalPart
			) AS VARCHAR(2))


Result
---------
OriginalWord											InDecimalNumber
Ninety Eight Crore Seventy Six Lac Fifty Four Thousand Three Hundred Twenty One and Twelve	987654321.12

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. The article has taught us at the bare minimum -

Use of SQL Server 2016 STRING_SPLIT function
Usage of Recursive CTE
Usage of For XML Path
Usage of Stuff Function
Usage of IIF
and many more...

Thanks for reading. Zipped file attached.

About the Author

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

Bookmark It

Login to vote for this post.

Latest Articles

Comments or Responses

Comment using

(Author doesn't get notification)