Amount in Word in MS SQL

Question

1.04/5 (4 votes)

See more:

Can me suggest how can convert amount in word in MS SQL, Pls send me reply by ASAP.

Thanks in advance!

Posted 12-Sep-11 2:23am

anjankant

Updated 29-Dec-19 7:16am

Add a Solution

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

3 solutions

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

Posted 29-Dec-19 7:17am

Member 14191904

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Muhammad Shahid Farooq · Accepted Answer · 2011-09-12T03:36:00

Step 1: 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
/*******************************************************************************/

Step 2: Execute the query
select dbo.Num_ToWords (1234)

Result: One thousand two hundred thirty-four

palraj001 · Accepted Answer · 2011-09-12T03:14:00

Hi..

First Create this table,

SQL

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

SQL

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

Amount in Word in MS SQL

3 solutions

Solution 2

Solution 1

Solution 6

Add your solution here

Preview 0