Generate Number to Words between 1 to 100

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 274
By using Recursive CTE we can generate number to words. The below is an example of generating numbers to Words between 1 to 100 by using recursive CTE


;WITH generateNumbersFrom1To19AND100CTE 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' UNION ALL SELECT 100 , 'One Hundred')

--Generate Numbers From 20 to 99
,generateNumbersFrom20To99 AS(

SELECT Rn=20
UNION ALL
SELECT Rn=Rn+1
FROM GenerateNumbersFrom20To99 WHERE Rn<99)

,numberTableFrom1to100CTE AS(

SELECT * FROM generateNumbersFrom1To19AND100CTE
UNION ALL

SELECT
Rn
,NumberToWords=

IIF(Rn/10 = 2,'Twenty '
,IIF(Rn/10 = 3,'Thrirty '
,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 numberTableFrom1to100CTE
ORDER BY 1

/*
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 Thrirty
31 Thrirty One
32 Thrirty Two
33 Thrirty Three
34 Thrirty Four
35 Thrirty Five
36 Thrirty Six
37 Thrirty Seven
38 Thrirty Eight
39 Thrirty 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
100 One Hundred
*/

Comments or Responses

Login to post response