Re: International Formats for Currencies. TO_CHAR wont work

You can use the number format elements G and D:

v734> alter session set nls_territory='France'; v734> SELECT TO_CHAR(123456.78,'000G000G009D9') from dual;

TO_CHAR(123456

v734> alter session set nls_territory='United Kingdom'; v734> SELECT TO_CHAR(123456.78,'000G000G009D9') from dual;

TO_CHAR(123456

v734> alter session set nls_territory='Italy';

v734> SELECT TO_CHAR(123456.78,'000G000G009D9') from dual;

TO_CHAR(123456

v734> alter session set nls_territory='Bulgaria'; v734> SELECT TO_CHAR(123456.78,'000G000G009D9') from dual;

TO_CHAR(123456

--
Have a nice day
Michel


Chris Webb <chris.webb_at_zoom.co.uk> a écrit dans le message :
87ebt6$oph$1_at_taliesin2.netcom.net.uk...


> Hi there,


>

> I am storing currency amounts in a database that is going to be used across

> a number of different european countries.

>

> The problem that I have is that different countries display currencies in

> different formats. eg:

> In the UK:    123,456.78

> In France:    123.456,78

> In Italy:    12.345.678    (they dont have any 'cents')

>

> the NLS_NUMERIC_CHARACTERS as defined in the database are "." for the

> decimal separator and "," for the thousands.

>

> What I want to be able to do is to return from an SQL the amount formatted

> in a particular national currency format.

>

> I started off using TO_CHAR, but that fails if the format string is invalid

> for the settings in NLS_NUMERIC_CHARACTERS.

>

> eg SELECT TO_CHAR(123456.78,'000.000.009,99') FROM DUAL

> comes up with the error ORA-01481:  Invalid number format model.

>

> I thought that you could use SUBSTR to get each block of three digits and

> insert a 'thousand separator' in between them, but if anyone has any better

> ideas, i'd be eternally grateful!

>

> Thanks in anticipation;

>

> Chris...

>

>