Home » SQL & PL/SQL » SQL & PL/SQL » how to convert "110000" into "one hundred ten thousand"
how to convert "110000" into "one hundred ten thousand" [message #4115] Sat, 09 November 2002 11:52 Go to next message
Muhammad Ahmad
Messages: 30
Registered: November 2002
Member
Dear Oracle Gurus,
I would like to convert numbers into Alphabets like if I input "305350" then the functions returns "Three Hundred Five Thousand Three Hundred Fifty".
Is there any oracle builtin available? or any other finest solution for this task?
Thanks and Regards,

Muhammad Ahmad

Report message to a moderator

Re: how to convert "110000" into "one hundred ten thousand" [message #4116 is a reply to message #4115] Sat, 09 November 2002 16:40 Go to previous messageGo to next message
Trifon Anguelov
Messages: 514
Registered: June 2002
Senior Member
There is not such function ready to use, but you can create PL/SQL function to do that.

User SUBSTR() to get the number position, then DECODE() to decode the number to character value, then append the character values for the final string. Return it from the function. Easy job...

Hope that helps,

clio_usa
OCP - DBA

Visit our Web site

Report message to a moderator

Re: how to convert "110000" into "one hundred ten thousand" [message #4121 is a reply to message #4115] Sat, 09 November 2002 23:05 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No need for a custom routine if you are only spelling numbers less than or equal to 5,373,484.

to_char(to_date(:num, 'J'), 'Jsp')


See here if you need to spell larger numbers:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1407603857650

Report message to a moderator

Re: how to convert "110000" into "one hundred ten thousand" [message #4154 is a reply to message #4121] Tue, 12 November 2002 17:51 Go to previous messageGo to next message
Muhammad Ahmad
Messages: 30
Registered: November 2002
Member
Thanks Bari,
It works and it really works fine, but if i have the figure more than "5,373,484" then what should i do?

Thanks for your kind support.

Ahmad.

Report message to a moderator

Re: how to convert "110000" into "one hundred ten thousand" [message #4210 is a reply to message #4154] Mon, 18 November 2002 16:42 Go to previous message
Barbara Boehmer
Messages: 9091
Registered: November 2002
Location: California, USA
Senior Member
You need to use the link that Todd already provided. If you go to that web page, you will see that Tom Kyte has provided a detailed answer to your question, which includes usage of a function for spelling larger numbers. I have provided the same link below in a different way, so that all you have to do is click on "Spell the number".

Report message to a moderator

Previous Topic: temp variables and assignments probs
Next Topic: Execute SQL*Loader from Stored Proc
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Apr 29 05:46:53 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.