14,664,496 members
Rate this:
See more:
Can me suggest how can convert amount in word in MS SQL, Pls send me reply by ASAP.

Posted
Updated 29-Dec-19 7:16am
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:

## 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:

## 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:

## 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)

Top Experts
Last 24hrsThis month
 OriginalGriff 110 Richard MacCutchan 100 allagi.arun 30 Sandeep Mewara 30 fjdiewornncalwe 10
 OriginalGriff 4,343 Richard MacCutchan 3,095 CPallini 1,930 Sandeep Mewara 1,375 Patrice T 1,104

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