SQL - Converting Numbers to Words in SQL Server

Here is the SQL Function to Convert Digit into Word:

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

RETURNS VARCHAR(max)

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:

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:

DECLARE @WORD VARCHAR(300)

SELECT @WORD = ''

DECLARE @M_AMT01  INT, @M_AMT02 INT

IF @AMOUNT < 1000000000 AND @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 < 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 + '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 + 'Rupees Only'

-- STEP 4:

--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 @WORD

END


Result:

select [dbo].[fn_DigitToWord](3250)

Output: "Three Thousand  Two Hundred Fifty Rupees Only"