conversion of number to character..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

conversion of number to character..

Author

Topic

arifshaik
Starting Member

5 Posts

Posted - 2006-03-01 : 04:17:01

hi experts..
im trying to convert number into character..like.

input - 4560 then
output should be - four thousand five hundred and sixty only with '$' symbol..

i can give any lenght of number..
pls send me the stored procedure code or query..
thanks in advacne..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-01 : 04:36:33

see here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11157

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-01 : 05:07:46

Alternative you can show words in Reports if you use.

Madhivanan

Failing to plan is Planning to fail

arifshaik
Starting Member

5 Posts

Posted - 2006-03-01 : 06:27:06

Hi every body..
thanks for giving reply..
But in ur reply,ur using only select statement..but i want
stored procedure which accpets the paramenter like - 4570 and
it should return like - four thousand five hundred and seventy only..
there is no table or temp.table..

pls send me a stored procedure or else function ..
its very urgent for me..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-01 : 07:48:02

you can easily convert the script to stored procedure or function.

----------------------------------
'KH'

arifshaik
Starting Member

5 Posts

Posted - 2006-03-02 : 00:54:55

Hi every body thanks for ur reply..Im new to sql and i got the solution for number to character conversion..I got the function.
i was convert that in to stored procedure..but im getting small data type conversion error..pls help me..
code is:
--------
create procedure AmountToWords (
@p_Number numeric(18,2)
)
as
begin

/*
CREATE function AmountToWords
(
@InNumber Numeric(18,2)
)
--Returns the number as words.
returns VARCHAR(2000)
as
BEGIN */

--SEt NoCount ON

Declare @Num Varchar(20)
Declare @Dec Varchar(3)
Declare @Return Varchar(2000)

select @Return = convert (varchar(2000),@p_Number)

Set @Dec = SubString(Convert(Varchar(20),@p_Number),Len(Convert(Varchar(20),@p_Number))-2,3)
Set @Num = SubString(Convert(Varchar(20),@p_Number),1,Len(Convert(Varchar(20),@p_Number))-3)

Declare @Hundred Char(8)
Declare @HundredAnd Char(12)
Declare @Thousand Char(9)
Declare @Lakh Char(5)
Declare @Lakhs Char(6)
Declare @Crore Char(6)
Declare @Crores Char(7)

Set @Hundred = 'Hundred '
Set @Thousand = 'Thousand '
Set @Lakh = 'Lakh '
Set @Lakhs = 'Lakhs '
Set @Crore = 'Crore '
Set @Crores = 'Crores '
Set @HundredAnd = 'Hundred and '

if Len(@Num) = 1 -- One
Begin
Set @Return = dbo.GetTextValue(@Num)
End

Else

if Len(@Num) = 2 -- Ten
Begin
Set @Return = dbo.GetTextValue(@Num)
End

Else

if Len(@Num) = 3 -- Hundred
Begin
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Hundred
IF SubString(@num,2,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2))
End

Else

if Len(@Num) = 4 -- thousand
Begin
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Thousand
If SubString(@Num,2,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,1)) + @Hundred
IF SubString(@num,3,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2))
End

Else

if Len(@Num) = 5 -- Ten Thousand
Begin
Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Thousand
If SubString(@Num,3,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,1)) + @Hundred
IF SubString(@num,4,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2))
End

Else

if Len(@Num) = 6 -- Lakh
Begin
If SubString(@Num,1,1) = '1'
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakh
Else
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakhs

If SubString(@Num,2,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Thousand
If SubString(@Num,4,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,1)) + @Hundred
IF SubString(@num,5,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2))
End

Else

if Len(@Num) = 7 -- Ten Lakhs
Begin

Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Lakhs
If SubString(@Num,3,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Thousand
If SubString(@Num,6,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,1)) + @Hundred
IF SubString(@num,6,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2))
End

Else

if Len(@Num) = 8 -- Crore
Begin
Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Crore
If SubString(@Num,2,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Lakhs

If SubString(@Num,4,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Thousand
If SubString(@Num,6,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,1)) + @Hundred
IF SubString(@num,7,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2))
End

Else

if Len(@Num) = 9 -- Ten Crore
Begin
Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Crores
If SubString(@Num,3,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Lakhs
If SubString(@Num,5,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Thousand
If SubString(@Num,7,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,1)) + @Hundred
IF SubString(@num,8,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2))
End

Else

if Len(@Num) = 10 -- Hundred Crore
Begin
Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Hundred
IF Substring(@Num,2,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2))
Set @Return = @Return + @Crores

If SubString(@Num,4,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Lakhs
If SubString(@Num,6,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Thousand
If SubString(@Num,8,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,1)) + @Hundred
IF SubString(@num,9,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2))
End

Else

if Len(@Num) = 11 -- Thousand Crore
Begin
Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Thousand
IF SubString(@Num,2,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,1)) + @Hundred
IF Substring(@Num,3,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2))

Set @Return = @Return + @Crores

If SubString(@Num,5,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Lakhs
If SubString(@Num,7,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Thousand
If SubString(@Num,9,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,1)) + @Hundred
IF SubString(@num,10,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2))
End

Else

if Len(@Num) = 12 -- Ten thousand Crore
Begin

Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Thousand
IF SubString(@Num,3,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,1)) + @Hundred
IF Substring(@Num,4,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2))

Set @Return = @Return + @Crores

If SubString(@Num,6,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Lakhs
If SubString(@Num,8,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Thousand
If SubString(@Num,10,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,1)) + @Hundred
IF SubString(@num,11,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,2))
End

Else

if Len(@Num) = 13 -- Lakh Crore
Begin

Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Lakh
If Substring(@Num,2,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,2)) + @Thousand
IF SubString(@Num,4,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(Substring(@Num,4,1)) + @Hundred
IF Substring(@Num,5,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2))

Set @Return = @Return + @Crores

If SubString(@Num,7,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Lakhs
If SubString(@Num,9,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2)) + @Thousand
If SubString(@Num,11,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,1)) + @Hundred
IF SubString(@num,12,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,2))

End

Else

if Len(@Num) = 14 -- Ten Lakh Crore
Begin
Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Lakhs
If Substring(@Num,3,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,2)) + @Thousand
IF SubString(@Num,5,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(Substring(@Num,5,1)) + @Hundred
IF Substring(@Num,6,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2))

Set @Return = @Return + @Crores

If SubString(@Num,8,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Lakhs
If SubString(@Num,10,2) <> '00'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2)) + @Thousand
If SubString(@Num,12,1) <> '0'
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,1)) + @Hundred
IF SubString(@num,13,2) <> '00'
Set @Return = @Return + 'And '
Set @Return = @Return + dbo.GetTextValue(SubString(@Num,13,2))

End

If @Dec <> '.00'

Set @Return = @Return + 'And ' + dbo.GetTextValue(SubString(@Dec,2,2)) + 'Paise'

Return @Return -----> I think error is here.
End
-----------------------------------------------------
exec AmountToWords 230 -->execution of s.p.

error is:
---------
Syntax error converting the varchar value 'Two Hundred And Thirty ' to a column of data type int.

pls give ur suggestions im new to sql..bye n thanks in advance..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 01:25:04

It will be easier if you use a function to do this. Change from Stored Procedure to function
" Return @Return -----> I think error is here."
For stored procedure, you can only return integer via the RETURN.
To return data out use the OUTPUT parameter.
Sample :



create procedure test_sp
@input	int,
@output	int OUTPUT
as
begin
	select @output = @input + @input
end
GO

declare
	@data	int
exec test_sp 10, @data OUTPUT
select @data

[/code]

----------------------------------
'KH'

arifshaik
Starting Member

5 Posts

Posted - 2006-03-02 : 01:48:52

Hi khtan...but i need to write stored procedure only..
thanks for giving reply..but in my function,where i need to change.
i didnt get ur sample s.p..bcoz it is also returng the number only na.. pls tell me the solution..

thanks in advance...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 01:55:51

[code]create procedure AmountToWords (
@p_Number numeric(18,2),
@Return Varchar(2000) OUTPUT
)
as
begin
...
...
~~Return @Return -----> I think error is here.~~
End[/code]
-----------------------------------------------------
declare @word varchar(2000)
exec AmountToWords 230, @word OUTPUT -->execution of s.p.

----------------------------------
'KH'

arifshaik
Starting Member

5 Posts

Posted - 2006-03-02 : 02:05:59

Hi khtan..
Thanks for reply..i got it..i wrote like this..

select '$' + @Return + 'Only' as 'MoneyName'

It is also working..this one is also correct naaaaa ??
for ur solution,while exec.we have to pass 2 parameters na??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 02:09:35

if you need to prefix the words by '$'
you can do it at the end of the stored procedure

select @return = '$' + @return + ' Only'

"for ur solution,while exec.we have to pass 2 parameters na??"
yes. That's why i suggested using a function

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 05:55:21

Well. Where do you want to show the word?. If you use Reports like Crystal Reports, you can make use of ToWords Function which is very easy and dont need to write complex sql statements

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources