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

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 2/15/2017 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@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 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: 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
, Numbers
FROM
(SELECT Crore,Lac,Thousand,Hundred, TensAndUnit
FROM transformDigitsIntoUnitarySystem) p
UNPIVOT
(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 sd
JOIN 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	InWords
823456.21 Eight Lac Twenty Three Thousand Four Hundred Fifty Six and Twenty One Paise Only


*/

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response