How to convert following Screnario

Posted by Jayakumars under ASP.NET AJAX on 12/14/2016 | Points: 10 | Views : 356 | Status : [Member] [MVP] | Replies : 11
Hi

How to create function Decimal to words. in Sql Server Only

I need following output 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: A2H on: 12/14/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can make use of below user defined functions to convert numbers to words in rupees and paise

First Create the below function which will convert Crore values to words
CREATE FUNCTION  [dbo].[UDF_NumberToWords] (@intNumberValue INTEGER)  
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @strNumberString VARCHAR(9)
DECLARE @strReturn VARCHAR(2000)
DECLARE @intUnits SMALLINT

-- Create table of number groups
DECLARE @tblNumberGroups TABLE (Units SMALLINT, Hundreds SMALLINT, Tens SMALLINT)

-- Handle errors and 'quick wins'
IF @intNumberValue IS NULL RETURN NULL
IF ISNUMERIC(@intNumberValue)=0 RETURN NULL
IF @intNumberValue = 0 RETURN 'ZERO'

IF @intNumberValue < 0
BEGIN
SET @strReturn='MINUS '
SET @intNumberValue=ABS(@intNumberValue)
END

SET @intUnits =0

-- Populate table of number groups
WHILE (@intNumberValue % 1000) > 0 OR (@intNumberValue/1000) >0
BEGIN
INSERT INTO @tblNumberGroups (Units, Hundreds, Tens) VALUES (@intUnits, (@intNumberValue % 1000)/100, (@intNumberValue % 1000) % 100 )
SELECT @intNumberValue = CAST (@intNumberValue / 1000 AS INTEGER)
SET @intUnits = @intUnits + 1
END

-- Remove last unit added
SET @intUnits = @intUnits-1

-- Concatenate text number by reading number groups in reverse order
SELECT @strReturn = ISNULL(@strReturn,' ') +
ISNULL(
ISNULL((CASE Hundreds
WHEN 1 THEN 'ONE HUNDRED '
WHEN 2 THEN 'TWO HUNDRED '
WHEN 3 THEN 'THREE HUNDRED '
WHEN 4 THEN 'FOUR HUNDRED '
WHEN 5 THEN 'FIVE HUNDRED '
WHEN 6 THEN 'SIX HUNDRED '
WHEN 7 THEN 'SEVEN HUNDRED '
WHEN 8 THEN 'EIGHT HUNDRED '
WHEN 9 THEN 'NINE HUNDRED '
END),' ') +
CASE WHEN (Hundreds >0 OR Units<@intUnits) AND Tens > 0 THEN ' AND ' ELSE ' ' END +
ISNULL((CASE Tens / 10
WHEN 2 THEN 'TWENTY '
WHEN 3 THEN 'THIRTY '
WHEN 4 THEN 'FORTY '
WHEN 5 THEN 'FIFTY '
WHEN 6 THEN 'SIXTY '
WHEN 7 THEN 'SEVENTY '
WHEN 8 THEN 'EIGHTY '
WHEN 9 THEN 'NINETY '
END),' ') +
ISNULL((CASE Tens
WHEN 10 THEN 'TEN '
WHEN 11 THEN 'ELEVEN '
WHEN 12 THEN 'TWELVE '
WHEN 13 THEN 'THIRTEEN '
WHEN 14 THEN 'FOURTEEN '
WHEN 15 THEN 'FIFTEEN '
WHEN 16 THEN 'SIXTEEN '
WHEN 17 THEN 'SEVENTEEN '
WHEN 18 THEN 'EIGHTEEN '
WHEN 19 THEN 'NINETEEN '
END),' ') +
COALESCE(
CASE WHEN Tens %10 =1 AND Tens / 10 <> 1 THEN 'ONE ' END,
CASE WHEN Tens %10 =2 AND Tens / 10 <> 1 THEN 'TWO ' END,
CASE WHEN Tens %10 =3 AND Tens / 10 <> 1 THEN 'THREE ' END,
CASE WHEN Tens %10 =4 AND Tens / 10 <> 1 THEN 'FOUR ' END,
CASE WHEN Tens %10 =5 AND Tens / 10 <> 1 THEN 'FIVE ' END,
CASE WHEN Tens %10 =6 AND Tens / 10 <> 1 THEN 'SIX ' END,
CASE WHEN Tens %10 =7 AND Tens / 10 <> 1 THEN 'SEVEN ' END,
CASE WHEN Tens %10 =8 AND Tens / 10 <> 1 THEN 'EIGHT ' END,
CASE WHEN Tens %10 =9 AND Tens / 10 <> 1 THEN 'NINE ' END,
' ')+
COALESCE(
CASE WHEN Units=1 AND (Hundreds>0 OR Tens>0) THEN 'THOUSAND ' END,
CASE WHEN Units=2 AND (Hundreds>0 OR Tens>0) THEN 'MILLION ' END,
CASE WHEN Units=3 AND (Hundreds>0 OR Tens>0) THEN 'BILLION ' END,
CASE WHEN Units=4 AND (Hundreds>0 OR Tens>0) THEN 'TRILLION ' END,
' ')
,' ')
FROM @tblNumberGroups
ORDER BY units DESC

-- Get rid of all the spaces
WHILE CHARINDEX(' ', @strReturn)>0
BEGIN
SET @strReturn = REPLACE(@strReturn,' ',' ')
END

SET @strReturn = LTRIM(RTRIM(@strReturn))

RETURN @strReturn
END



Now you can create the below function which will convert the value to numbers

CREATE FUNCTION [dbo].[UDF_NumericToRupees]( @RUPEES AS DECIMAL(30,2))
RETURNS VARCHAR(2000)
AS
BEGIN

DECLARE @INNTBL_01 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))
DECLARE @INNTBL_02 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))
DECLARE @AMOUNT NUMERIC(38,0)
DECLARE @PAISE AS INT
SET @AMOUNT = FLOOR(@RUPEES)
SET @PAISE = CAST(CONVERT(DECIMAL(10,0),(@RUPEES % 1) * 100 ) AS INT)
--STEP 2:
--NOW ADD UP THE SALT AND PEPPER ONTO TABLE I.E. INSERT THE WORDS INTO IT
INSERT INTO @INNTBL_01 SELECT 'One '
INSERT INTO @INNTBL_01 SELECT 'Two '
INSERT INTO @INNTBL_01 SELECT 'Three '
INSERT INTO @INNTBL_01 SELECT 'Four '
INSERT INTO @INNTBL_01 SELECT 'Five '
INSERT INTO @INNTBL_01 SELECT 'Six '
INSERT INTO @INNTBL_01 SELECT 'Seven '
INSERT INTO @INNTBL_01 SELECT 'Eight '
INSERT INTO @INNTBL_01 SELECT 'Nine '
INSERT INTO @INNTBL_01 SELECT 'Ten '
INSERT INTO @INNTBL_01 SELECT 'Eleven '
INSERT INTO @INNTBL_01 SELECT 'Twelve '
INSERT INTO @INNTBL_01 SELECT 'Thirteen '
INSERT INTO @INNTBL_01 SELECT 'Fourteen '
INSERT INTO @INNTBL_01 SELECT 'Fifteen '
INSERT INTO @INNTBL_01 SELECT 'Sixteen '
INSERT INTO @INNTBL_01 SELECT 'Seventeen '
INSERT INTO @INNTBL_01 SELECT 'Eighteen '
INSERT INTO @INNTBL_01 SELECT 'Nineteen '
INSERT INTO @INNTBL_01 SELECT 'Twenty '
-- SIMILARY, INSERT THE MULTIPLES
INSERT INTO @INNTBL_02 SELECT 'Ten '
INSERT INTO @INNTBL_02 SELECT 'Twenty '
INSERT INTO @INNTBL_02 SELECT 'Thirty '
INSERT INTO @INNTBL_02 SELECT 'Forty '
INSERT INTO @INNTBL_02 SELECT 'Fifty '
INSERT INTO @INNTBL_02 SELECT 'Sixty '
INSERT INTO @INNTBL_02 SELECT 'Seventy '
INSERT INTO @INNTBL_02 SELECT 'Eighty '
INSERT INTO @INNTBL_02 SELECT 'Ninety '
--STEP 3:
----CHECK FOR THE LIMIT OF THE AMOUNT I.E. WHAT IS THE PLACE VALUE OF DIGITS -- LACS, THOUSANDS OR HUNDREDS
DECLARE @WORD VARCHAR(2000)
SELECT @WORD = ''
DECLARE @M_AMT01 BigInt, @M_AMT02 BigInt
IF @AMOUNT >= 10000000 BEGIN
SET @M_AMT01 = @AMOUNT
SELECT @AMOUNT = ( @AMOUNT % 10000000 )
SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 10000000
DECLARE @WORD1 VARCHAR(300)
SET @WORD1 = ''
IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN
SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )
END
SET @WORD =dbo.UDF_NumberToWords(@M_AMT01) + ' Crore '
END
IF @AMOUNT < 10000000 AND @AMOUNT >= 100000 BEGIN
SET @M_AMT01 = @AMOUNT
SELECT @AMOUNT = ( @AMOUNT % 100000 )
SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 100000
SET @WORD1 = ''
IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN
SET @M_AMT02 = @M_AMT01
SET @M_AMT01 = ( @M_AMT01 % 10)
SET @M_AMT02 = ( @M_AMT02-@M_AMT01 ) / 10
SET @WORD1 = ( SELECT @WORD1 + MTEXT

Thanks,
A2H
My Blog

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

Posted by: A2H on: 12/14/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can call the function like below

select dbo.UDF_NumericToRupees(823456.21)


Source URL : http://stackoverflow.com/questions/29362038/how-to-convert-numeric-value-to-indian-rupees-words-in-sql

Thanks,
A2H
My Blog

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

Posted by: Jayakumars on: 12/18/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

your second post not completed .

How can i check this.

But also you set mark as answer

this is wrong answer

can you update this

Mark as Answer if its helpful to you

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

Posted by: Jayakumars on: 12/18/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

your stackoverflow url code also not working

Mark as Answer if its helpful to you

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

Posted by: A2H on: 12/19/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hello Jayakumars,

Could you please provide some details about the execption which you are facing. I have retesed the same again with the value you provided (823456.21) and it works with out any issues

As a troubleshooting steps Please confirm the below

Did you create both Functions " UDF_NumericToRupees" and "UDF_NumberToWords" on proper database. we use these two functions to convert number to words in sql server.



Thanks,
A2H
My Blog

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

Posted by: A2H on: 12/19/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
Additionaly you mentioned "But also you set mark as answer "

No I didnt mark my post as answer. Since you have raised the question only you or Moderators can Mark the post as answer.

Thanks,
A2H
My Blog

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

Posted by: Jayakumars on: 12/20/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

can you fixed this one . I am posted new one follow this url

http://www.dotnetfunda.com/forums/show/22109/how-to-convert-this-situation-in-sql-server

Mark as Answer if its helpful to you

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

Posted by: A2H on: 12/20/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Jayakumars,

I am sorry I still couldnt get the actualy issue you are facing, when I restested the script its working exactly like the requirement.
You can find the screenshot in this link : http://i.imgur.com/RDTCHz2.png

Thanks,
A2H
My Blog

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

Posted by: Jayakumars on: 12/23/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hello

how to run your function without error

check your function this top line

CREATE FUNCTION [dbo].[UDF_NumericToRupees]( @RUPEES AS DECIMAL(30,2))



bottom line this


SET @M_AMT02 = ( @M_AMT02-@M_AMT01 ) / 10
SET @WORD1 = ( SELECT @WORD1 + MTEXT

here in completed here also no return value

i meet error when i execute this function

can you update your function this




Mark as Answer if its helpful to you

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

Posted by: Jayakumars on: 12/23/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
A2H

can you post full function code for this

CREATE FUNCTION [dbo].[UDF_NumericToRupees]( @RUPEES AS DECIMAL(30,2))


Mark as Answer if its helpful to you

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

Posted by: A2H on: 12/23/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
Oh I got the problem now. I apologize for the inconvenience caused due to this.

Please use the updated code

/****** Object:  UserDefinedFunction [dbo].[DSP_NumericToRupees]    Script Date: 04/02/2015 18:43:07 ******/  
CREATE FUNCTION [dbo].[UDF_NumericToRupees]( @RUPEES AS DECIMAL(30,2))
RETURNS VARCHAR(2000)
AS
BEGIN

DECLARE @INNTBL_01 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))
DECLARE @INNTBL_02 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))
DECLARE @AMOUNT NUMERIC(38,0)
DECLARE @PAISE AS INT
SET @AMOUNT = FLOOR(@RUPEES)
SET @PAISE = CAST(CONVERT(DECIMAL(10,0),(@RUPEES % 1) * 100 ) AS INT)
--STEP 2:
--NOW ADD UP THE SALT AND PEPPER ONTO TABLE I.E. INSERT THE WORDS INTO IT
INSERT INTO @INNTBL_01 SELECT 'One '
INSERT INTO @INNTBL_01 SELECT 'Two '
INSERT INTO @INNTBL_01 SELECT 'Three '
INSERT INTO @INNTBL_01 SELECT 'Four '
INSERT INTO @INNTBL_01 SELECT 'Five '
INSERT INTO @INNTBL_01 SELECT 'Six '
INSERT INTO @INNTBL_01 SELECT 'Seven '
INSERT INTO @INNTBL_01 SELECT 'Eight '
INSERT INTO @INNTBL_01 SELECT 'Nine '
INSERT INTO @INNTBL_01 SELECT 'Ten '
INSERT INTO @INNTBL_01 SELECT 'Eleven '
INSERT INTO @INNTBL_01 SELECT 'Twelve '
INSERT INTO @INNTBL_01 SELECT 'Thirteen '
INSERT INTO @INNTBL_01 SELECT 'Fourteen '
INSERT INTO @INNTBL_01 SELECT 'Fifteen '
INSERT INTO @INNTBL_01 SELECT 'Sixteen '
INSERT INTO @INNTBL_01 SELECT 'Seventeen '
INSERT INTO @INNTBL_01 SELECT 'Eighteen '
INSERT INTO @INNTBL_01 SELECT 'Nineteen '
INSERT INTO @INNTBL_01 SELECT 'Twenty '
-- SIMILARY, INSERT THE MULTIPLES
INSERT INTO @INNTBL_02 SELECT 'Ten '
INSERT INTO @INNTBL_02 SELECT 'Twenty '
INSERT INTO @INNTBL_02 SELECT 'Thirty '
INSERT INTO @INNTBL_02 SELECT 'Forty '
INSERT INTO @INNTBL_02 SELECT 'Fifty '
INSERT INTO @INNTBL_02 SELECT 'Sixty '
INSERT INTO @INNTBL_02 SELECT 'Seventy '
INSERT INTO @INNTBL_02 SELECT 'Eighty '
INSERT INTO @INNTBL_02 SELECT 'Ninety '
--STEP 3:
----CHECK FOR THE LIMIT OF THE AMOUNT I.E. WHAT IS THE PLACE VALUE OF DIGITS -- LACS, THOUSANDS OR HUNDREDS
DECLARE @WORD VARCHAR(2000)
SELECT @WORD = ''
DECLARE @M_AMT01 BigInt, @M_AMT02 BigInt
IF @AMOUNT >= 10000000 BEGIN
SET @M_AMT01 = @AMOUNT
SELECT @AMOUNT = ( @AMOUNT % 10000000 )
SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 10000000
DECLARE @WORD1 VARCHAR(300)
SET @WORD1 = ''
IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN
SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )
END
SET @WORD =dbo.UDF_NumberToWords(@M_AMT01) + ' Crore '
END
IF @AMOUNT < 10000000 AND @AMOUNT >= 100000 BEGIN
SET @M_AMT01 = @AMOUNT
SELECT @AMOUNT = ( @AMOUNT % 100000 )
SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 100000
SET @WORD1 = ''
IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN
SET @M_AMT02 = @M_AMT01
SET @M_AMT01 = ( @M_AMT01 % 10)
SET @M_AMT02 = ( @M_AMT02-@M_AMT01 ) / 10
SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )
END
IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN
SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )
END
SET @WORD = @WORD + @WORD1 + 'Lakh '
END
IF @AMOUNT < 100000 AND @AMOUNT >= 1000 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 1000 )
SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 1000
SET @WORD1 = ''
IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN
SET @M_AMT02 = @M_AMT01
SET @M_AMT01 = ( @M_AMT01 % 10 )
SET @M_AMT02 = ( @M_AMT02-@M_AMT01 ) / 10
SET @WORD1 = ( SELECT @WORD1 + MTEXT + '' FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )
END
IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN
SET @WORD1 = ( SELECT @WORD1 + MTEXT +'' FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )
END
SET @WORD = @WORD + @WORD1 + 'Thousand '
END
IF @AMOUNT < 1000 AND @AMOUNT > = 100 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 100 )
SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 100
SET @WORD = ( SELECT @WORD + ' ' +MTEXT + 'Hundred ' FROM @INNTBL_01 WHERE RECNO = @M_AMT01)
END
IF @AMOUNT < 100 AND @AMOUNT > 20 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 10 )
SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 10
SET @WORD = ( SELECT @WORD + MTEXT + '' FROM @INNTBL_02 WHERE RECNO = @M_AMT01 )
END
IF @AMOUNT <= 20 AND @AMOUNT >= 1 BEGIN
SET @WORD = ( SELECT @WORD + MTEXT +'' FROM @INNTBL_01 WHERE RECNO = @AMOUNT )
END

--Change
--SET @WORD = @WORD + 'rupees '
SET @WORD = @WORD + ' '
-- STEP 4:
-- CALCULATE THE PAISE ALSO.
DECLARE @WORDP VARCHAR(300)
SET @WORDP = ''
IF @PAISE <> 0 BEGIN
IF @PAISE < 100 AND @PAISE > 20 BEGIN
DECLARE @PAISE_01 VARCHAR(300)
SET @PAISE_01 = @PAISE
SET @PAISE = ( @PAISE % 10 )
SET @PAISE_01 = ( @PAISE_01-@PAISE ) / 10
SET @WORDP = ( SELECT @WORDP + MTEXT FROM @INNTBL_02 WHERE RECNO = @PAISE_01 )
END
IF @PAISE <= 20 AND @PAISE >= 1 BEGIN
SET @WORDP = ( SELECT @WORDP + MTEXT FROM @INNTBL_01 WHERE RECNO = @PAISE )
END
SET @WORD = @WORD + 'and ' + @WORDP + 'paise'
END
IF @AMOUNT>=1000000000 BEGIN
SET @WORD = ''
END
Return UPPER(@WORD + ' ONLY')
END


Thanks,
A2H
My Blog

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

Login to post response