# How to convert following Screnario

Posted by Jayakumars under ASP.NET AJAX on 12/14/2016 | Points: 10 | Views : 318 | 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`

#### Responses

Posted by: A2H on: 12/14/2016 [Member] [MVP] Silver | Points: 25
 0 You can make use of below user defined functions to convert numbers to words in rupees and paiseFirst 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 @strReturnEND` 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)ASBEGINDECLARE @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 INTSET @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 ITINSERT 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 MULTIPLESINSERT 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 HUNDREDSDECLARE @WORD VARCHAR(2000)SELECT @WORD = ''DECLARE @M_AMT01 BigInt, @M_AMT02 BigIntIF @AMOUNT >= 10000000 BEGINSET @M_AMT01 = @AMOUNTSELECT @AMOUNT = ( @AMOUNT % 10000000 )SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 10000000DECLARE @WORD1 VARCHAR(300)SET @WORD1 = ''IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGINSET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )ENDSET @WORD =dbo.UDF_NumberToWords(@M_AMT01) + ' Crore 'ENDIF @AMOUNT < 10000000 AND @AMOUNT >= 100000 BEGINSET @M_AMT01 = @AMOUNTSELECT @AMOUNT = ( @AMOUNT % 100000 )SET @M_AMT01 = ( @M_AMT01-@AMOUNT ) / 100000SET @WORD1 = ''IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGINSET @M_AMT02 = @M_AMT01SET @M_AMT01 = ( @M_AMT01 % 10)SET @M_AMT02 = ( @M_AMT02-@M_AMT01 ) / 10SET @WORD1 = ( SELECT @WORD1 + MTEXT Thanks, A2H My BlogJayakumars, if this helps please login to Mark As Answer. | Alert Moderator```
Posted by: A2H on: 12/14/2016 [Member] [MVP] Silver | Points: 25
 0 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 BlogJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jayakumars on: 12/18/2016 [Member] [MVP] Bronze | Points: 25
Posted by: Jayakumars on: 12/18/2016 [Member] [MVP] Bronze | Points: 25
Posted by: A2H on: 12/19/2016 [Member] [MVP] Silver | Points: 25
 0 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 BlogJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: A2H on: 12/19/2016 [Member] [MVP] Silver | Points: 25
 0 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 BlogJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jayakumars on: 12/20/2016 [Member] [MVP] Bronze | Points: 25
Posted by: A2H on: 12/20/2016 [Member] [MVP] Silver | Points: 25
 0 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 BlogJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jayakumars on: 12/23/2016 [Member] [MVP] Bronze | Points: 25
 0 Hellohow to run your function without errorcheck your function this top lineCREATE FUNCTION [dbo].[UDF_NumericToRupees]( @RUPEES AS DECIMAL(30,2))bottom line thisSET @M_AMT02 = ( @M_AMT02-@M_AMT01 ) / 10SET @WORD1 = ( SELECT @WORD1 + MTEXT here in completed here also no return value i meet error when i execute this functioncan you update your function thisMark as Answer if its helpful to youJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jayakumars on: 12/23/2016 [Member] [MVP] Bronze | Points: 25
 0 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 youJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: A2H on: 12/23/2016 [Member] [MVP] Silver | Points: 25
 0 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 BlogJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Latest Posts