# Generate Number to Words between 20 to 99

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

`--Generate Numbers From 20 to 99;WITH generateNumbersFrom20To99 AS(	SELECT Rn=20	UNION ALL	SELECT Rn=Rn+1	FROM GenerateNumbersFrom20To99 WHERE Rn<99)SELECT 	Number = 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`

Result
--------
`Number	NumberToWords20	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 Nine`