# Generate Number to Words between 1 to 100

Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 735
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 ALLSELECT 2 , 'Two' UNION ALLSELECT 3 , 'Three' UNION ALLSELECT 4 , 'Four' UNION ALLSELECT 5 , 'Five' UNION ALLSELECT 6 , 'Six'  UNION ALLSELECT 7 , 'Seven' UNION ALLSELECT 8 , 'Eight' UNION ALLSELECT 9 , 'Nine' UNION ALL SELECT 10 , 'Ten'  UNION ALLSELECT  11 , 'Eleven'  UNION ALLSELECT  12 , 'Twelve' UNION ALLSELECT  13 , 'Thirteen' UNION ALLSELECT  14 , 'Fourteen' UNION ALLSELECT  15 , 'Fifteen' UNION ALLSELECT  16 , 'Sixteen' UNION ALLSELECT  17 , 'Seventeen' UNION ALLSELECT  18 , 'Eighteen' UNION ALLSELECT  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 generateNumbersFrom1To19AND100CTEUNION ALLSELECT 	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 numberTableFrom1to100CTEORDER BY 1/*Digit	NumberToWords1	One2	Two3	Three4	Four5	Five6	Six7	Seven8	Eight9	Nine10	Ten11	Eleven12	Twelve13	Thirteen14	Fourteen15	Fifteen16	Sixteen17	Seventeen18	Eighteen19	Nineteen20	Twenty 21	Twenty One22	Twenty Two23	Twenty Three24	Twenty Four25	Twenty Five26	Twenty Six27	Twenty Seven28	Twenty Eight29	Twenty Nine30	Thrirty 31	Thrirty One32	Thrirty Two33	Thrirty Three34	Thrirty Four35	Thrirty Five36	Thrirty Six37	Thrirty Seven38	Thrirty Eight39	Thrirty Nine40	Fourty 41	Fourty One42	Fourty Two43	Fourty Three44	Fourty Four45	Fourty Five46	Fourty Six47	Fourty Seven48	Fourty Eight49	Fourty Nine50	Fifty 51	Fifty One52	Fifty Two53	Fifty Three54	Fifty Four55	Fifty Five56	Fifty Six57	Fifty Seven58	Fifty Eight59	Fifty Nine60	Sixty 61	Sixty One62	Sixty Two63	Sixty Three64	Sixty Four65	Sixty Five66	Sixty Six67	Sixty Seven68	Sixty Eight69	Sixty Nine70	Seventy 71	Seventy One72	Seventy Two73	Seventy Three74	Seventy Four75	Seventy Five76	Seventy Six77	Seventy Seven78	Seventy Eight79	Seventy Nine80	Eighty 81	Eighty One82	Eighty Two83	Eighty Three84	Eighty Four85	Eighty Five86	Eighty Six87	Eighty Seven88	Eighty Eight89	Eighty Nine90	Ninety 91	Ninety One92	Ninety Two93	Ninety Three94	Ninety Four95	Ninety Five96	Ninety Six97	Ninety Seven98	Ninety Eight99	Ninety Nine100	One Hundred*/`