# How to convert this situation in sql server [Resolved]

Posted by Jayakumars under ASP.NET AJAX on 12/18/2016 | Points: 10 | Views : 267 | Status : [Member] [MVP] | Replies : 1
Hi
How to convert numeric to words. any one post this before check and post .
When i solve my issue after i agree mark as answer.

how to implemented function for this

--823456.21
--Eight Lakh Twenty Three Thousand Four Hundread Fifty Six and Twenty one Paise Only

 0 @Jayakumars Sir,Here you go`DECLARE @Number VARCHAR(20) = '823456.21'DECLARE @RealPart INT = PARSENAME(@Number, 2)DECLARE @DecimalPart INT = PARSENAME(@Number, 1)-- 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 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' )--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 generateNumbersFrom1To19CTEUNION ALLSELECT 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: Divide the number into their digits, getDigitsCTE AS ( SELECT UnitarySystemPosition=1 ,Quotient = @RealPart / 10 ,Remainder = @RealPart % 10 UNION ALL SELECT UnitarySystemPosition=UnitarySystemPosition+1 ,Quotient / 10 , Quotient % 10 FROM getDigitsCTE WHERE Quotient > 0)-- Step 3: Position the result of Step 2 according to the unitary system.,transformDigitsIntoUnitarySystem AS(SELECT Crore= STUFF((SELECT '' + dw.Remainder + ' ' FROM getDigitsCTE dw WHERE UnitarySystemPosition IN(8,9) ORDER BY dw.UnitarySystemPosition DESC FOR XML PATH('')),1,0,'') ,Lac= STUFF((SELECT '' + dw.Remainder + ' ' FROM getDigitsCTE dw WHERE UnitarySystemPosition IN(6,7) ORDER BY dw.UnitarySystemPosition DESC FOR XML PATH('')),1,0,'') ,Thousand = STUFF((SELECT '' + dw.Remainder + ' ' FROM getDigitsCTE dw WHERE UnitarySystemPosition IN(4,5) ORDER BY dw.UnitarySystemPosition DESC FOR XML PATH('')),1,0,'') ,Hundred = STUFF((SELECT '' + dw.Remainder + ' ' FROM getDigitsCTE dw WHERE UnitarySystemPosition IN(3) ORDER BY dw.UnitarySystemPosition DESC FOR XML PATH('')),1,0,'') ,TensAndUnit = STUFF((SELECT '' + dw.Remainder + ' ' FROM getDigitsCTE dw WHERE UnitarySystemPosition IN(1,2) ORDER BY dw.UnitarySystemPosition DESC FOR XML PATH('')),1,0,''))-- Step 4: Label the numbers into the unitary system,labelNumbersInUnitarySystemCTE AS(SELECT SlNo=ROW_NUMBER() OVER(ORDER BY (SELECT 1)) , UnitarySystem , NumbersFROM (SELECT Crore,Lac,Thousand,Hundred, TensAndUnit FROM transformDigitsIntoUnitarySystem) pUNPIVOT (Numbers FOR UnitarySystem IN ( Crore,Lac,Thousand,Hundred, TensAndUnit))AS unpvt)--Step 5: Combine the Result of Step 1 and 4 to generate number to words,digitWordsCombinationCTE AS(SELECT sd.* ,NumberToWords=nd.NumberToWords + ' ' + IIF(sd.UnitarySystem = 'TensAndUnit','',sd.UnitarySystem)FROM labelNumbersInUnitarySystemCTE sdJOIN numberTableFrom1to99CTE nd ON nd.Digit = sd.Numbers)SELECT OriginalNumber=@Number ,InWords = --Real Part STUFF((SELECT '' + dw.NumberToWords + ' ' FROM digitWordsCombinationCTE dw ORDER BY dw.SlNo FOR XML PATH('')),1,0,'') --Decimal Part + ' and ' + ( SELECT n.NumberToWords FROM numberTableFrom1to99CTE n WHERE n.Digit = @DecimalPart ) + ' Paise Only'` /*`OriginalNumber InWords823456.21 Eight Lac Twenty Three Thousand Four Hundred Fifty Six and Twenty One Paise Only` */-- Thanks & Regards, RNA TeamJayakumars, if this helps please login to Mark As Answer. | Alert Moderator