Convert Words To Number using TSQL in SET Based Way

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

Post Article |

Search Articles |

Articles Home

Let's say we have One Hundred Thirty Four. We have to convert the words in number such that, the desire output will be 134. 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 Number using TSQL in SET Based Way

Introduction

Let's say we have One Hundred Thirty Four. We have to convert the words in number such that, the desire output will be 134. In this article, we will look into the conversion of the same using TSQL in SET Based Way. In the process we will look into the usage of the STRING_SPLIT function of SQL Server 2016. This article is the opposite of the article Convert Number To Words using TSQL in SET Based Way.

Step by step approach to solve

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: Split the words based on their unitary system position

-- Step 2: Split the words based on their unitary system position
DECLARE @words VARCHAR(100) = 'One Hundred Thirty Four'

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

SELECT *
FROM splitWordsAndPositionByUNITARYSystemCTE

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

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

--Step 3: Combine the Result of Step 1 and 2 to generate words to number(s)
--Step 3: Combine the Result of Step 1 and 2 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

SELECT 
    OriginalWord=@words
    ,InNumber = 
        STUFF((SELECT '' + 
        dw.Digit + ' ' 
FROM digitWordsCombinationCTE dw
ORDER BY dw.UnitarySystemPosition 
FOR XML PATH('')),1,0,'')

Result
---------
OriginalWord		InNumber
One Hundred Thirty Four	134

Complete TSQL Script

DECLARE @words VARCHAR(100) = 'Thirty Four Crore Fifty Two Lac Thirty Seven Thousand Eight Hundred Nineteen'

-- 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: 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(@words, 'Crore', '.'),'Lac','.'),'Thousand','.'),'Hundred','.'), '.')
)

--Step 3: Combine the Result of Step 1 and 2 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
    ,InNumber = 
        STUFF((SELECT '' + 
        dw.Digit + ' ' 
FROM digitWordsCombinationCTE dw
ORDER BY dw.UnitarySystemPosition 
FOR XML PATH('')),1,0,'')


Result
---------
OriginalWord									InNumber
Thirty Four Crore Fifty Two Lac Thirty Seven Thousand Eight Hundred Nineteen	345237819

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)