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