Click here to Skip to main content
14,664,496 members
Rate this:
Please Sign up or sign in to vote.
Can me suggest how can convert amount in word in MS SQL, Pls send me reply by ASAP.

Thanks in advance!
Posted
Updated 29-Dec-19 7:16am
Comments
rkthiyagarajan 12-Sep-11 8:27am
   
Why to need that convert in SQL?..You do in asp.net and save it to your Database

Rate this:
Please Sign up or sign in to vote.

Solution 1

Hi..


First Create this table,
CREATE TABLE [dbo].[M_Words](
    [Code] [int] NULL,
    [WNumber] [int] NULL,
    [Wwords] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]



then

INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(1,0,'Zero')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(2,1,'One')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(3,2,'Two')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(4,3,'Three')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(5,4,'Four')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(6,5,'Five')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(7,6,'Six')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(8,7,'Seven')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(9,8,'Eight')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(10,9,'Nine')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(11,10,'Ten')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(12,11,'Eleven')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(13,12,'Twelve')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(14,13,'Thirteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(15,14,'Fourteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(16,15,'Fifteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(17,16,'Sixteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(18,17,'Seventeen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(19,18,'Eighteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(20,19,'Nineteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(21,20,'Twenty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(22,30,'Thirty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(23,40,'Forty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(24,50,'Fifty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(25,60,'Sixty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(26,70,'Seventy')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(27,80,'Eighty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(28,90,'Ninety')


then write this Function



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





create function [dbo].[usp_fnNumToWords](@Number Numeric(18,2),@CPaise Char(1))

 returns varchar(5000)

	BEGIN
	Declare @StrNumber varchar(10), @SLacs char(2), @SThou char(2), @SHun char(2)
	Declare @STenUnt char(2), @STen char(2), @SUnt char(2), @SDecimal char(2)
	Declare @ILacs Int, @IThou Int, @IHun Int, @ITenUnt Int, @ITen Int, @IUnt Int, @IDecimal Int
	Declare @SNumToWords varchar(100), @Wwords varchar(10)






	Select @StrNumber = Replicate('0',10-Len(LTrim(RTrim(convert(varchar,@Number))))) + LTrim(RTrim(Convert(varchar,@Number)))
	--Print @StrNumber
	--Print Len(@StrNumber)
	Select @SNumToWords = ''

	--Print Len(LTrim(RTrim(convert(varchar,@Number))))
	If Len(LTrim(RTrim(convert(varchar,@Number)))) > 4
	Begin
		--Print Len(@StrNumber)
		Select @SLacs = Substring(@StrNumber,1,2)
		--Print @SLacs
		Select @ILacs = Convert(int,@SLacs)
		If @ILacs > 0
		Begin
			Select @STen = Substring(@StrNumber,1,1)
			Select @SUnt = Substring(@StrNumber,2,1)

			if Convert(int,@STen) = 1 
			Begin
				Select @ITen = Convert(int,Substring(@StrNumber,1,2))
				Select @IUnt = 0
			End
			Else
			Begin
				Select @ITen = Convert(int,@STen)*10
				Select @IUnt = Convert(int,@SUnt)
			End

			If @ITen > 0 
			Begin			
				Select @Wwords = ''
				Select @Wwords = Wwords From M_Words Where WNumber = @ITen
				--Print @Wwords
				Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
			End

			If @IUnt > 0 
			Begin			
				Select @Wwords = ''
				Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
				--Print @Wwords
				Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
			End

			--Select @Wwords = ''
			--Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
			--Print @Wwords
			Select @SNumToWords = @SNumToWords + ' Lacs'
		End

		Select @SThou = Substring(@StrNumber,3,2)
		--Print @SThou
		Select @IThou = Convert(int,@SThou)
		If @IThou > 0
		Begin
			Select @STen = Substring(@StrNumber,3,1)
			Select @SUnt = Substring(@StrNumber,4,1)

			if Convert(int,@STen) = 1 
			Begin
				Select @ITen = Convert(int,Substring(@StrNumber,3,2))
				Select @IUnt = 0
			End
			Else
			Begin
				Select @ITen = Convert(int,@STen)*10
				Select @IUnt = Convert(int,@SUnt)
			End
			
			If @ITen > 0 
			Begin			
				Select @Wwords = ''
				Select @Wwords = Wwords From M_Words Where WNumber = @ITen
				--Print @Wwords
				Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
			End

			If @IUnt > 0 
			Begin			
				Select @Wwords = ''
				Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
				--Print @Wwords
				Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
			End

			--Select @Wwords = ''
			--Select @Wwords = Wwords From M_Words Where WNumber = @IThou
			--Print @Wwords
			--Select @SNumToWords = @SNumToWords + @Wwords + ' Thousand '
			Select @SNumToWords = @SNumToWords + ' Thousand '
		End

		Select @SHun = Substring(@StrNumber,5,1)
		--Print @SHun
		Select @IHun = Convert(int,@SHun)
		If @IHun > 0
		Begin
			Select @Wwords = ''
			Select @Wwords = Wwords From M_Words Where WNumber = @IHun
			--Print @Wwords
			Select @SNumToWords = @SNumToWords + @Wwords + ' Hundred'
		End

		Select @STenUnt = Substring(@StrNumber,6,2)
		---Print @STenUnt

		Select @ITenUnt = Convert(int,@STenUnt)

		If @ITenUnt > 0
		Begin
			Select @STen = Substring(@StrNumber,6,1)
			Select @SUnt = Substring(@StrNumber,7,1)

			if Convert(int,@STen) = 1 
			Begin
				Select @ITen = Convert(int,Substring(@StrNumber,6,2))
				Select @IUnt = 0
			End
			Else
			Begin
				Select @ITen = Convert(int,@STen)*10
				Select @IUnt = Convert(int,@SUnt)
			End

			
			If @ITen > 0 
			Begin			
				Select @Wwords = ''
				Select @Wwords = Wwords From M_Words Where WNumber = @ITen
				--Print @Wwords
				Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
			End

			If @IUnt > 0 
			Begin			
				Select @Wwords = ''
				Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
				--Print @Wwords
				Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
			End
		End

		Select @SNumToWords = @SNumToWords + Space(1) + 'Rupees'		--Only/-



	End
	Else
	Begin
		--Print Len(@StrNumber)
		--Print LTrim(RTrim(convert(varchar,@Number)))
		Select @SLacs = Substring(LTrim(RTrim(convert(varchar,@Number))),1,1)
		--Print @SLacs
		Select @ILacs = Convert(int,@SLacs)
		If @ILacs > 0 and @ILacs <> 1
		Begin
			Select @Wwords = ''
			Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
			--Print @Wwords
			Select @SNumToWords = @SNumToWords + Space(1) + @Wwords + Space(1) + 'Rupees'
		End
		Else
		Begin
			Select @Wwords = ''
			Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
			--Print @Wwords
			Select @SNumToWords = @SNumToWords + @Wwords + Space(1) + 'Rupee'
		End
	End

	If @CPaise = 'Y'
	Begin
		Select @SDecimal = Substring(@StrNumber,9,2)
		Select @IDecimal = Convert(int,@SDecimal)
		If @IDecimal > 0
		Begin
			Select @SNumToWords = @SNumToWords + ' and'
			Select @STen = Substring(@SDecimal,1,1)
			Select @SUnt = Substring(@SDecimal,2,1)
	
			if Convert(int,@STen) = 1 
			Begin
				Select @ITen = Convert(int,Substring(@StrNumber,9,2))
				Select @IUnt = 0
			End
			Else
			Begin
				Select @ITen = Convert(int,@STen)*10
				Select @IUnt = Convert(int,@SUnt)
			End
			
			If @ITen > 0 
			Begin			
				Select @Wwords = ''
				Select @Wwords = Wwords From M_Words Where WNumber = @ITen
				--Print @Wwords
				Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
			End
	
			If @IUnt > 0 
			Begin			
				Select @Wwords = ''
				Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
				--Print @Wwords
				Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
			End
	
			Select @SNumToWords = @SNumToWords + Space(1) + 'Paise'	
	
		End
	End

	--Print LTrim(RTrim(@SNumToWords))
return LTrim(RTrim(@SNumToWords))
END





select like this

select dbo.usp_fnNumToWords(105422.00 ,'Y')


Y- with ps
N-without ps



Regards,
pal
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

<b>Step 1:</b> Create a function
Copy the below text into Query analyzer and execute it

/*******************************************************************************/
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO

CREATE FUNCTION dbo.Num_ToWords (@Number Numeric (38, 0))
RETURNS VARCHAR(8000)
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
, @outputString = ''
, @counter = 1
SELECT @length = LEN(@inputNumber)
, @position = LEN(@inputNumber) - 2
, @loops = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'
UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'
UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'
UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'
UNION ALL SELECT '09', 'nine' UNION ALL SELECT '10', 'ten'
UNION ALL SELECT '11', 'eleven' UNION ALL SELECT '12', 'twelve'
UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
UNION ALL SELECT '15', 'fifteen' UNION ALL SELECT '16', 'sixteen'
UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'
UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'
UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'
UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

-- get chunks of 3 numbers at a time, padded with leading zeros
SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

IF @chunk <> '000' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
, @hundreds = SUBSTRING(@chunk, 1, 1)
, @tens = SUBSTRING(@chunk, 2, 1)
, @ones = SUBSTRING(@chunk, 3, 1)

-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE @tensones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END
ELSE BEGIN -- break down the ones and the tens separately

SET @outputString = ' '
+ (SELECT word
FROM @NumbersTable
WHERE @tens + '0' = number)
+ '-'
+ (SELECT word
FROM @NumbersTable
WHERE '0'+ @ones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END

-- now get the hundreds
IF @hundreds <> '0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE '0' + @hundreds = number)
+ ' hundred '
+ @outputString
END
END

SELECT @counter = @counter + 1
, @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the result
END
GO

GRANT EXEC on dbo.udf_Num_ToWords TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*******************************************************************************/

<b>Step 2:</b> Execute the query
select dbo.Num_ToWords (1234)

<b>Result:</b> One thousand two hundred thirty-four
   
Rate this:
Please Sign up or sign in to vote.

Solution 6

create FUNCTION [dbo].[fnGetWordsValue]
(
@Number Decimal(10,2)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @InWords VARCHAR(50)
if @Number = 0
set @InWords = 'Zero'
if @Number = 1
set @InWords = 'One'
if @Number = 2
set @InWords = 'Two'
if @Number = 3
set @InWords = 'Three'
if @Number = 4
set @InWords = 'Four'
if @Number = 5
set @InWords = 'Five'
if @Number = 6
set @InWords = 'Six'
if @Number = 7
set @InWords = 'Seven'
if @Number = 8
set @InWords = 'Eight'
if @Number = 9
set @InWords = 'Nine'
if @Number = 10
set @InWords = 'Ten'
if @Number = 11
set @InWords = 'Eleven'
if @Number = 12
set @InWords = 'Twelve'
if @Number = 13
set @InWords = 'Thirteen'
if @Number = 14
set @InWords = 'Fourteen'
if @Number = 15
set @InWords = 'Fifteen'
if @Number = 16
set @InWords = 'Sixteen'
if @Number = 17
set @InWords = 'Seventeen'
if @Number = 18
set @InWords = 'Eighteen'
if @Number = 19
set @InWords = 'Nineteen'
if @Number = 20
set @InWords = 'Twenty'
if @Number = 30
set @InWords = 'Thirty'
if @Number = 40
set @InWords = 'Forty'
if @Number = 50
set @InWords = 'Fifty'
if @Number = 60
set @InWords = 'Sixty'
if @Number = 70
set @InWords = 'Seventy'
if @Number = 80
set @InWords = 'Eighty'
if @Number = 90
set @InWords = 'Ninety'
RETURN @InWords
End






create FUNCTION [dbo].[fnConvertNumToWords]
(
@Number NUMERIC(18, 2),
@CPaise CHAR(1)
)
RETURNS VARCHAR(2000)
AS
BEGIN
Declare @minusNumber NUMERIC(18, 2);

if (@Number < 0)
BEGIN
SET @minusNumber=ABS(@Number);
SET @Number=ABS(@Number);

RETURN '';

END
DECLARE @StrNumber VARCHAR(10),
@SLacs CHAR(2),
@SThou CHAR(2),
@SHun CHAR(2)

DECLARE @STenUnt CHAR(2),
@STen CHAR(2),
@SUnt CHAR(2),
@SDecimal CHAR(2)

DECLARE @ILacs INT,
@IThou INT,
@IHun INT,
@ITenUnt INT,
@ITen INT,
@IUnt INT,
@IDecimal INT

DECLARE @SNumToWords VARCHAR(100),
@numberInWord VARCHAR(10)

SELECT @StrNumber = REPLICATE('0', 10 - LEN(LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))))
+ LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))

SELECT @SNumToWords = ''
IF LEN(LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))) > 4
BEGIN
SELECT @SLacs = SUBSTRING(@StrNumber, 1, 2)
SELECT @ILacs = CONVERT(INT, @SLacs)
IF @ILacs > 0
BEGIN
SELECT @STen = SUBSTRING(@StrNumber, 1, 1)
SELECT @SUnt = SUBSTRING(@StrNumber, 2, 1)
IF CONVERT(INT, @STen) = 1
BEGIN
SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 1, 2))
SELECT @IUnt = 0
END
ELSE
BEGIN
SELECT @ITen = CONVERT(INT, @STen) * 10
SELECT @IUnt = CONVERT(INT, @SUnt)
END
IF @ITen > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@ITen)

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END

IF @IUnt > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@IUnt)

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
SELECT @SNumToWords = @SNumToWords + ' Lacs'
END

SELECT @SThou = SUBSTRING(@StrNumber, 3, 2)

SELECT @IThou = CONVERT(INT, @SThou)
IF @IThou > 0
BEGIN
SELECT @STen = SUBSTRING(@StrNumber, 3, 1)
SELECT @SUnt = SUBSTRING(@StrNumber, 4, 1)
IF CONVERT(INT, @STen) = 1
BEGIN
SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 3, 2))
SELECT @IUnt = 0
END
ELSE
BEGIN
SELECT @ITen = CONVERT(INT, @STen) * 10
SELECT @IUnt = CONVERT(INT, @SUnt)
END
IF @ITen > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@ITen)

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END

IF @IUnt > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@IUnt)

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END

SELECT @SNumToWords = @SNumToWords + ' Thousand '
END

SELECT @SHun = SUBSTRING(@StrNumber, 5, 1)
SELECT @IHun = CONVERT(INT, @SHun)
IF @IHun > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@IHun)

SELECT @SNumToWords = @SNumToWords + @numberInWord + ' Hundred'
END

SELECT @STenUnt = SUBSTRING(@StrNumber, 6, 2)

SELECT @ITenUnt = CONVERT(INT, @STenUnt)
IF @ITenUnt > 0
BEGIN
SELECT @STen = SUBSTRING(@StrNumber, 6, 1)
SELECT @SUnt = SUBSTRING(@StrNumber, 7, 1)
IF CONVERT(INT, @STen) = 1
BEGIN
SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 6, 2))
SELECT @IUnt = 0
END
ELSE
BEGIN
SELECT @ITen = CONVERT(INT, @STen) * 10
SELECT @IUnt = CONVERT(INT, @SUnt)
END
IF @ITen > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@ITen)

--Select @SNumToWords = @SNumToWords + Space(1) + @numberInWord
SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END

IF @IUnt > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@IUnt)


SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
END

SELECT @SNumToWords = @SNumToWords + SPACE(1) + 'Rupees'
END
ELSE
BEGIN
SELECT @SLacs = SUBSTRING(LTRIM(RTRIM(CONVERT(VARCHAR, @Number))), 1, 1)
SELECT @ILacs = CONVERT(INT, @SLacs)
IF @ILacs > 0
AND @ILacs <> 1
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@ILacs)

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord + SPACE(1) +
'Rupees'
END
ELSE
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@ILacs)

SELECT @SNumToWords = @SNumToWords + @numberInWord + SPACE(1) + 'Rupee'
END
END
IF @CPaise = 'Y'
BEGIN
SELECT @SDecimal = SUBSTRING(@StrNumber, 9, 2)
SELECT @IDecimal = CONVERT(INT, @SDecimal)
IF @IDecimal > 0
BEGIN
SELECT @SNumToWords = @SNumToWords + ' and'
SELECT @STen = SUBSTRING(@SDecimal, 1, 1)
SELECT @SUnt = SUBSTRING(@SDecimal, 2, 1)
IF CONVERT(INT, @STen) = 1
BEGIN
SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 9, 2))
SELECT @IUnt = 0
END
ELSE
BEGIN
SELECT @ITen = CONVERT(INT, @STen) * 10
SELECT @IUnt = CONVERT(INT, @SUnt)
END
IF @ITen > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@ITen)

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END

IF @IUnt > 0
BEGIN
SELECT @numberInWord = ''
SELECT @numberInWord = dbo.fnGetWordsValue (@IUnt)

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END

SELECT @SNumToWords = @SNumToWords + SPACE(1) + 'Paise'
END
END
RETURN LTRIM(RTRIM(@SNumToWords))+ ' Only'

END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100