CREATE FUNCTION [dbo].[GetNumberToWords] (@intNumberValue INTEGER)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @strNumberString VARCHAR(9)
DECLARE @strReturn VARCHAR(2000)
DECLARE @intUnits SMALLINT
-- Alter 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
GO
----------------------------------********************************----------------------------------------
CREATE FUNCTION [dbo].[GetNumericToRupees] (@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.GetNumberToWords(@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
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 + 'paisa'
END
IF @AMOUNT >= 1000000000
BEGIN
SET @WORD = ''
END
SELECT @WORD = REPLACE(@WORD COLLATE Latin1_General_BIN, ' ', ' ');
Return UPPER(@WORD + ' ONLY')
END
------------------------------------
Go
SELECT dbo.GetNumericToRupees(625)
