How to convert following Screnario

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

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

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

Kumaraspcode2009@gmail.com

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

Hi

your stackoverflow url code also not working

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

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

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

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

Kumaraspcode2009@gmail.com

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

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

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

Kumaraspcode2009@gmail.com

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

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

Kumaraspcode2009@gmail.com

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

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