International Formats for Currencies. TO_CHAR wont work
Date: Thu, 3 Feb 2000 17:38:48 -0000
Message-ID: <87ebt6$oph$1_at_taliesin2.netcom.net.uk>
[Quoted] 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... Received on Thu Feb 03 2000 - 18:38:48 CET