if i give number either in Rs or in dollars distinguishing by commas(,) with no limt to the number how to convert into words as normal as we spell the number.. plz help me in writing procedure. i am using sql server 2000. expecting your friendly hands..

---
ali

Is this what you want to do?

Convert to this.
1,300 One Thousand Three Hundred

ofcourse its a part as if i give 123405.20$ it should display as
One hundred and twenty-three thousand four hundred five dollars and twenty cents. if i give same as 123405.20 Rs it should display as one lakh twenty three thousand four hundred and five rupees twenty paise..
----
ali

I don't know of any function in SQL that does that. I suppose you could use a lookup table, split the string into its different parts, use that to look up the translation.

Do you know how to split a string in SQL? If not look up LEN, SUBSTRING, CHARINDEX, RIGHT and LEFT in the BOL. I haven't actually tried this specific scenario but it just might work.

You'll have to create a table with the tranlation from 1 - 100.

Number Translation
1 one
2 two
3 three
etc.

Split up the number, query the table, concatenate with the words 'thousand', 'hundred', etc., capitalize the first letter and print it.

Here's a start. Its not great but it will give you the general idea.

declare @thousands varchar(3), @hundredthousands varchar(25), @hundreds varchar(3), @cents varchar(2), @theValue varchar(20), @theTotalLength int, @theFinalString varchar(200)

Set @theValue = '123405.20'
Set @theTotalLength = Len(@thevalue)

IF LEN(@theValue) > 6
SET @thousands = REVERSE(SUBSTRING(REVERSE(@theValue),7,3))

If LEN(@thousands) > 2
Set @hundredthousands = LEFT(@thousands,1)


IF LEN(@hundredthousands) > 0
    SET @hundredthousands = (select trans from trans where numbers = @hundredthousands)
IF LEN(@hundredthousands) > 0
    select @hundredthousands + ' hundred'

thanks for suggestion and i will try ..