November 21, 2008 at 2:44 am
hi friends
i want to convert money value in the term of word through query
example
1000 (rupees) to one thousand
please help me ..urgent
November 21, 2008 at 3:19 am
yogaanand.me (11/21/2008)
hi friendsi want to convert money value in the term of word through query
example
1000 (rupees) to one thousand
please help me ..urgent
I'm not sure, there is no built in procedure for your need in SQL Server. You can write down your own way.
November 21, 2008 at 4:09 am
yogaanand.me (11/21/2008)
hi friendsi want to convert money value in the term of word through query
example
1000 (rupees) to one thousand
please help me ..urgent
here is the procedure. this calculates upto 1000, extend the logic based on the if structure, it can optimize and i m optimzing it, but till then you can use it.
alter PROCEDURE converNumtoText
@number INT
as
declare @text Varchar(8000)
declare @num1 INT
declare @len INT
declare @numstring varchar(8000)
declare @numLength INT
SET @numLength = @number
SET @text = ''
SET @len = 0
SET @numstring = ''
WHILE @len < len(@numLength)
BEGIN
SET @text = ''
IF @number = 1
SET @text = 'One'
else IF @number = 2
SET @text = 'Two'
else IF @number = 3
SET @text = 'Three'
else IF @number = 4
SET @text = 'Four'
else IF @number = 5
SET @text = 'Five'
else IF @number = 6
SET @text = 'Six'
else IF @number = 7
SET @text = 'Seven'
else IF @number = 8
SET @text = 'Eight'
else IF @number = 9
SET @text = 'Nine'
else IF @number = 10
SET @text = 'Ten'
else IF @number = 11
SET @text = 'Eleven'
else IF @number = 12
SET @text = 'Twelve'
else IF @number = 13
SET @text = 'Thirteen'
else IF @number = 14
SET @text = 'Fourteen'
else IF @number = 15
SET @text = 'Fifteen'
else if @number = 16
SET @text = 'Sixteen'
else if @number = 17
SET @text = 'Seventeen'
else if @number = 18
SET @text = 'Eightteen'
else IF @number = 19
SET @text = 'Nineteen'
else IF @number >= 20 and @number < 30
SET @text = 'Twenty'
else IF @number >= 30 and @number < 40
SET @text = 'Thirty'
else IF @number >= 40 and @number < 50
SET @text = 'Fourty'
else IF @number >= 50 and @number < 60
SET @text = 'Fifty'
else IF @number >= 60 and @number < 70
SET @text = 'Sixty'
else IF @number >= 70 and @number < 80
SET @text = 'Seventy'
else IF @number >= 80 and @number < 90
SET @text = 'Eighty'
else IF @number >= 90 and @number < 100
SET @text = 'Ninety'
else IF @number >= 100 and @number < 200
SET @text = 'One Hundred'
else IF @number >= 200 and @number < 300
SET @text = 'Two Hundred'
else IF @number >= 300 and @number < 400
SET @text = 'Three Hundred'
else IF @number >= 400 and @number < 500
SET @text = 'Four Hundred'
else IF @number >= 500 and @number < 600
SET @text = 'Five Hundred'
else IF @number >= 600 and @number < 700
SET @text = 'Six Hundred'
else IF @number >= 700 and @number < 800
SET @text = 'Seven Hundred'
else IF @number >= 800 and @number < 900
SET @text = 'Eight Hundred'
else if @number >= 900 and @number < 1000
SET @text = 'Nine Hundred'
else if @number = 1000
SET @text = 'One Thousand'
else if @number = 100000
SET @text = 'Lac'
if Len(@number) = 2 AND @number >= 20
Begin
SET @num1 = @number%10
SET @number = @num1
END
else if Len(@number) = 3
Begin
SET @num1 = @number%100
SET @number = @num1
END
else
begin
SET @num1 = 0
SET @number = @num1
END
SET @numstring = @numstring + ' ' + @text
IF @number = 0
SET @len = len(@numLength)
ELSE
SET @len = @len + 1
END
PRINT @numstring
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply