numeric to fully-written-numbers pl/sql translator

[Guy Hendrickx]

Hi,
I'm looking for a pl/sql procedure which is capable of translating
a numeric value to a full-text string :
ex. '20' will become 'twenty'.
If possible it should be available for Ducth, French and English.
If only one of the above languages is supported, I'm still interested.
Kind regards,
Guy Hendrickx
G...@Cereus.BE

[kais...@my-deja.com]

Hey!

I saw something where numbers were run through a julian date
function to derive their textual equivilant.

Took a minute, but found it on Peter Koletzke's Oracle Home Page
http://ourworld.compuserve.com/homepages/Peter_Koletzke/des2ktip.htm
Look at CONVERT NUMBERS TO WORDS

Later.. Rich

Disclaim: this is not my company's stuff.

In article <7k36da$glv$1...@naxos.belnet.be>,

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[Mark Gumbs]

Try this, i'm not sure about other languages though.
This kind of procedure is dotted around various web sites so you may find
another language somewhere.

============= Cut Here ===============

Function Spell (v_number in number) return varchar2 is

v_word varchar2(1000);

begin

select to_char(to_date(v_number,'j'), 'Jsp')
into v_word
from dual;

return v_word;

end;

============= END ===============

Mark

Guy Hendrickx wrote in message <7k36da$glv$1...@naxos.belnet.be>...

[Thomas Kyte]

A copy of this was sent to "Guy Hendrickx" <G...@Cereus.BE>
(if that email address didn't require changing)

On Mon, 14 Jun 1999 17:20:12 +0200, you wrote:

>Hi,
>I'm looking for a pl/sql procedure which is capable of translating
>a numeric value to a full-text string :
>ex. '20' will become 'twenty'.
>If possible it should be available for Ducth, French and English.
>If only one of the above languages is supported, I'm still interested.
>Kind regards,
>Guy Hendrickx
>G...@Cereus.BE
>

You can use a date function to do this....

1* select to_char( to_date(5373484,'J'),'Jsp') from dual
SQL> /

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four

This will work for numbers between 1 and 5,373,484...

You can take it a step further to support numbers -5,373,484 .. 5,373,484 by

select decode( sign( :N ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(:N) ), +1, to_char( to_date( abs(:N),'J'),'Jsp') )
from dual
/

should be international already (language support)

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th

Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation

[Kelly Young]

Guy Hendrickx wrote:
>
> Hi,
> I'm looking for a pl/sql procedure which is capable of translating
> a numeric value to a full-text string :
> ex. '20' will become 'twenty'.
> If possible it should be available for Ducth, French and English.
> If only one of the above languages is supported, I'm still interested.
> Kind regards,
> Guy Hendrickx
> G...@Cereus.BE

Here's an example of printing dollars and cents in English words.
Hope this helps.
/*
Oracle SQL example of converting numbers to words. Given a
table called "CHK" with a single column "NUM" - number(7,2):

NUM
_______
.34
1234.45
3445

ZERO DOLLARS AND THIRTY-FOUR CENTS
ONE THOUSAND TWO HUNDRED THIRTY-FOUR DOLLARS AND FORTY-FIVE CENTS
THREE THOUSAND FOUR HUNDRED FORTY-FIVE DOLLARS AND ZERO CENTS

*/
select decode(instr(num,'.'),
1,'ZERO',
to_char(to_date(substr(num,1,decode(instr(num,'.'),
0,length(num),
instr(num,'.')-1)),
'J'),
'JSP'))
||' DOLLARS AND '||
decode(length(num)-instr(num,'.'),
1,to_char(to_date(rpad(substr(num,instr(num,'.')+1,
length(num)),
2,0),
'J'),
'JSP'),
decode(instr(num,'.'),
0,'ZERO',

to_char(to_date(substr(num,instr(num,'.')+1,length(num)),
'J'),
'JSP')))
||' CENTS '
from chk;
--
Kelly Young
Maricopa Community Colleges
yo...@maricopa.edu

[Jonathan Lewis]

And if the syntax:

select to_char( to_date(5373484,'J'),'Jsp','nls_date_language=french') from
dual

did for the Jsp format what it does for all the other
character type formats you would be done.

select to_char( to_date(5373484,'J'),'Day','nls_date_language=french') from
dual;

TO_CHAR(
--------
Vendredi

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Thomas Kyte wrote in message <376e20e8...@newshost.us.oracle.com>...

[Jan-Marcel]

Thomas Kyte heeft geschreven in bericht
<376e20e8...@newshost.us.oracle.com>...

>A copy of this was sent to "Guy Hendrickx" <G...@Cereus.BE>
>(if that email address didn't require changing)

>On Mon, 14 Jun 1999 17:20:12 +0200, you wrote:
>
>>Hi,
>>I'm looking for a pl/sql procedure which is capable of translating
>>a numeric value to a full-text string :
>>ex. '20' will become 'twenty'.
>>If possible it should be available for Ducth, French and English.
>>If only one of the above languages is supported, I'm still interested.
>>Kind regards,
>>Guy Hendrickx
>>G...@Cereus.BE
>>
>

>You can use a date function to do this....
>
> 1* select to_char( to_date(5373484,'J'),'Jsp') from dual
>SQL> /
>

>TO_CHAR(TO_DATE(5373484,'J'),'JSP')
>--------------------------------------------------------------------------
>Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four
>
>

>This will work for numbers between 1 and 5,373,484...
>
>You can take it a step further to support numbers -5,373,484 .. 5,373,484
by
>
>select decode( sign( :N ), -1, 'Negative ', 0, 'Zero', NULL ) ||
> decode( sign( abs(:N) ), +1, to_char( to_date( abs(:N),'J'),'Jsp') )
>from dual
>/
>
>should be international already (language support)

>See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
Oracle8i'...
>Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
>
>Current article is "Fine Grained Access Control", added June 8'th
>
>Thomas Kyte tk...@us.oracle.com
>Oracle Service Industries Reston, VA USA
>--
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation

Hi Guy,

I'm sorry I have to disappoint you.
Unfortunately the format mask 'JSP' is not influenced by the
nls_date_language. It always returns in English.
However you can use the nls_date_language in combination with other date
format masks.

Good luck,

Jan-Marcel

[Peter L]

Thomas Kyte wrote in message <
>

>You can use a date function to do this....
>
> 1* select to_char( to_date(5373484,'J'),'Jsp') from dual
>SQL> /
>
>TO_CHAR(TO_DATE(5373484,'J'),'JSP')
>--------------------------------------------------------------------------
>Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four
>
>
>This will work for numbers between 1 and 5,373,484...
>

There are added complications if you are trying to do this in the UK. (I
don't know about other countries.) We have the 'and' in large numbers in
different places than the US. In the above an 'and' would be required after
each 'hundred'. We had to write a routine to anglify the results because we
couldn't persuade the business to go with the US format.

[Alton Ayers]

Go to http://www.revealnet.com/plsql-pipeline/archives.htm. There are a
couple of PL/SQL procedures there which may be useful to you.

-- Alton

[GDN]

And what about me,

poor developer in Belgium.

When i try to add the 'NLS_DATE_LANGUAGE=Dutch' to the to_char function I
still get my results
in english. According to Oracle Support, only the english language is
supported.

Still, it's a pretty good thing to know.

Greatings
Gert

Peter L <ne...@lenniep.freeserve.co.uk> wrote in article
<7kbd1h$hlf$2...@news5.svr.pol.co.uk>...