# Convert Words To Number using TSQL in SET Based Way

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

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.

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

1. Use of SQL Server 2016 STRING_SPLIT function
2. Usage of Recursive CTE
3. Usage of For XML Path
4. Usage of Stuff Function
5. Usage of IIF
6. and many more...

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.