Re: convert number to word ???
some where I got this create or replace function spell_number( p_number in number ) return varchar2 as type myArray is table of varchar2(255); l_str myArray := myArray( '', ' thousand ', ' million ', ' billion ', ' trillion ', ' quadrillion ', ' quintillion ', ' sextillion ', ' septillion ', ' octillion ', ' nonillion ', ' decillion ', ' undecillion ', ' duodecillion ' ); l_num varchar2(50) default trunc( p_number ); l_return varchar2(4000); begin for i in 1 .. l_str.count loop exit when l_num is null; if ( substr(l_num, length(l_num)-2, 3) 0 ) then l_return := to_char( to_date(substr(l_num, length(l_num)-2, 3),'J' ), 'Jsp' ) || l_str(i) || l_return; end if; l_num := substr( l_num, 1, length(l_num)-3 ); end loop; return l_return; end; / SQL select 2 spell_number( 12345678901234567890123456789012345678 ) 3 from dual; SPELL_NUMBER(1234567890123456789012345678901234567 -- Twelve undecillion Three Hundred Forty-Five decill ion Six Hundred Seventy-Eight nonillion Nine Hundr ed One octillion Two Hundred Thirty-Four septillio n Five Hundred Sixty-Seven sextillion Eight Hundre d Ninety quintillion One Hundred Twenty-Three quad rillion Four Hundred Fifty-Six trillion Seven Hund red Eighty-Nine billion Twelve million Three Hundr ed Forty-Five thousand Six Hundred Seventy-Eight a-k - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 22, 2003 2:19 PM Hi, Is there a function that can conver number to word? ef: 100 is ONE HUNDRED, 2003 is TWO THOUSAND AND THREE. Thank you in advanced!!! __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: convert number to word ???
You can find perl module doing that at: http://www.bayview.com/software/perl.shtml -- Mladen Gogala Oracle DBA -Original Message- Andrea Oracle Sent: Friday, August 22, 2003 5:19 PM To: Multiple recipients of list ORACLE-L Hi, Is there a function that can conver number to word? ef: 100 is ONE HUNDRED, 2003 is TWO THOUSAND AND THREE. Thank you in advanced!!! __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: convert number to word ???
I got this off of metalink: select to_char( to_date( 1, 'J' ), 'jsp' ) from dual; SQLselect to_char( to_date( 123,'J'), 'jsp') from dual; TO_CHAR(TO_DATE(123,'J') -- one hundred twenty-three SQLselect to_char( to_date( 123,'J'), 'Jsp') from dual; TO_CHAR(TO_DATE(123,'J') -- One Hundred Twenty-Three SQLselect to_char( to_date( 123,'J'), 'JSP') from dual; TO_CHAR(TO_DATE(123,'J') -- ONE HUNDRED TWENTY-THREE I suspect there is an upper limit imposed by some integer conversion or julian date thingy... Shannon Shannon St. DennisDatabase AdministratorCity of Regina(306) 777-7415 (phone)(306) 777-6804 (fax)[EMAIL PROTECTED] The degree of normality in a database is inversely proportional to that of its DBA [EMAIL PROTECTED] 08/22/03 03:19pm Hi,Is there a function that can conver number to word? ef: 100 is ONE HUNDRED, 2003 is TWO THOUSAND ANDTHREE. Thank you in advanced!!!__Do you Yahoo!?Yahoo! Calendar - Free online calendar with sync to Outlook(TM).http://calendar.yahoo.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Andrea Oracle INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: convert number to word ???
SELECT TO_CHAR(TO_DATE(2003,'J'),'JSP') FROM DUAL; TO_CHAR(TO_DATE(20 -- TWO THOUSAND THREE - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 22, 2003 4:19 PM Hi, Is there a function that can conver number to word? ef: 100 is ONE HUNDRED, 2003 is TWO THOUSAND AND THREE. Thank you in advanced!!! __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle-L INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: convert number to word ???
-Original Message- AK Sent: Friday, August 22, 2003 3:09 PM To: Multiple recipients of list ORACLE-L some where I got this create or replace function spell_number( p_number in number ) return varchar2 . . . That is the same routine i was directed to a few months back when I asked this question. It is from the ask tom site. It worked perfectly, and the use of to_date was brilliant and elegant. Seeing routines like that reminds me of just how far I have to go to be good at what I do. Steve McClure -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: convert number to word ???
This trick is rather limited, as it runs out of steam at amounts somewhat greater than my salary. Best go to asktom.oracle.com for this, I recall seeing a rather good one there that had much higher limits. Jared Shannon St. Dennis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/22/2003 03:29 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: convert number to word ??? I got this off of metalink: select to_char( to_date( 1, 'J' ), 'jsp' ) from dual; SQL select to_char( to_date( 123,'J'), 'jsp') from dual; TO_CHAR(TO_DATE(123,'J') -- one hundred twenty-three SQL select to_char( to_date( 123,'J'), 'Jsp') from dual; TO_CHAR(TO_DATE(123,'J') -- One Hundred Twenty-Three SQL select to_char( to_date( 123,'J'), 'JSP') from dual; TO_CHAR(TO_DATE(123,'J') -- ONE HUNDRED TWENTY-THREE I suspect there is an upper limit imposed by some integer conversion or julian date thingy... Shannon Shannon St. Dennis Database Administrator City of Regina (306) 777-7415 (phone) (306) 777-6804 (fax) [EMAIL PROTECTED] The degree of normality in a database is inversely proportional to that of its DBA [EMAIL PROTECTED] 08/22/03 03:19pm Hi, Is there a function that can conver number to word? ef: 100 is ONE HUNDRED, 2003 is TWO THOUSAND AND THREE. Thank you in advanced!!! __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
