How to change numbers to words in SQL?

Printable View

Jul 13th, 2004, 06:59 AM
moinkhan

How to change numbers to words in SQL?

I want to display the numbers in Words in SQL

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

Any idea?
Jul 13th, 2004, 07:04 AM
Keithuk

Try One Thousand Two Hundred and Four. :thumb:
Jul 13th, 2004, 07:21 AM
mendhak

You write code for it. Google is your friend, you should find stuff.
Jul 13th, 2004, 07:26 AM
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
Jul 13th, 2004, 09:43 AM
jhermiz

Quote:

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
Jul 15th, 2004, 01:29 AM
moinkhan

BOL? :confused:
Jul 15th, 2004, 01:49 AM
AvisSoft

Quote:

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!
Jul 15th, 2004, 07:56 AM
jhermiz

Quote:

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.
Jul 15th, 2004, 08:03 AM
AvisSoft

Where do i get copy of BOL ?
Jul 15th, 2004, 08:07 AM
jhermiz

Quote:

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
Jul 18th, 2004, 12:09 AM
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..

Quote:

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!
Jul 18th, 2004, 02:47 AM
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 . . .
Jul 18th, 2004, 08:07 AM
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..