How to change numbers to words in SQL?

[moinkhan]

I want to display the numbers in Words in SQL

e.g. 1204 should be displayed as One thousand and four..

Any idea?

[Keithuk]

Try One Thousand Two Hundred and Four.

[mendhak]

You write code for it. Google is your friend, you should find stuff.

[moinkhan]

I found this statement and it's working fine.. can somebody explain it to me?

Select to_char(to_date(to_char(99999,'999999999','J'),'JSP') From Dual

[jhermiz]

Originally posted by moinkhan
I found this statement and it's working fine.. can somebody explain it to me?

Select to_char(to_date(to_char(99999,'999999999','J'),'JSP') From Dual

Try BOL

[moinkhan]

BOL?

[AvisSoft]

Originally posted by moinkhan
I found this statement and it's working fine.. can somebody explain it to me?

Select to_char(to_date(to_char(99999,'999999999','J'),'JSP') From Dual

Hi!

You mean that it works ? And you're using VB right ? I made a stupid non working function for this...and it was not perfect then i stopped working on that. If this works then why should i have another stupid function.

Can you explain more how it works please ?

Thanks!

[jhermiz]

Originally posted by AvisSoft
Hi!

You mean that it works ? And you're using VB right ? I made a stupid non working function for this...and it was not perfect then i stopped working on that. If this works then why should i have another stupid function.

Can you explain more how it works please ?

Thanks!

Do you have client tools SQL installed ?

DO you have a copy of BOL ? Books online...

Refer to that it gives great examples and explanations of what you are trying to do.

[AvisSoft]

Where do i get copy of BOL ?

[jhermiz]

Originally posted by AvisSoft
Where do i get copy of BOL ?

It comes wiht client and tools
or go here:

http://www.microsoft.com/sql/techinf...2000/books.asp

[moinkhan]

I haven't checked this with VB... although because it is the standard SQL Function.. should work with VB...

The explanation is easy...
to_char(to_date(to_char(99999,'999999999','J'),'JSP')

in this the innermost to_char is found useless... so u can also remove that. now this reads

to_char(to_date(12345,'J'),'JSP')

the to_date function changes this number into Julian Date format..... and then to_char function uses J(Julian format) SP(Spell) switches to convert this Julian date into words...
I did it on SQL Plus environment in Oracle.. haven't yet checked with VB... but i hope it should also work there..

Originally posted by AvisSoft
Hi!

You mean that it works ? And you're using VB right ? I made a stupid non working function for this...and it was not perfect then i stopped working on that. If this works then why should i have another stupid function.

Can you explain more how it works please ?

Thanks!

[Vishalgiri]

Hence it is a Classic Visual basic Forum

I suggest you to make a Function which returns a string and arguments passed to function is integer.

and in that function first typecast that integer to string and find its length then using right() take first integer to the right and then a select case if it is 1 then replace 1 with "One" Proceeding in this way the second chr from right if it is 2 then replace it with "Twenty " for third chr if it is 1 then replace it with One hundred.......

Make some this kind of logic . . .

[moinkhan]

I did it with VB a long ago.... but now i needed a pure SQL solution.... n i got that by myself.. thanx for your suggestion..