Hi Tom,
Is there any way of of replicating the Excel number format '#9.99' in Oracle, where the '#' expands to the required number of nines.
I would like to write something like
select to_char(number, '#9.99') from dual;
but I don't know in advance how large the number will be, and putting say 10 or 15 nines makes the code less legible, and may at some point not be enough.
I managed this:
select to_char(number, ltrim(RPAD(' ',ceil(log(10, number))+1 ,'9'))||'.99') from dual;but that would be even less legible... :(
The objective is to be able to simply copy and paste data into Excel, from a Toad query, without modifying number format parameters at either end, knowing that I work in a multinational company with a mix of language versions, and that the PC that I'm using any given day may be set up in different ways, and Toad usually gives me decimal commas when Excel expects decimal points (but not always). Si basically I want to display all output numbers with decimal points rather than commas, whatever their length. But I also don't want to overload the query (which is already very long as it is) with needless '9999999999999.99' format strings if something shorter exists.
Thanks,
Chris
In SQL*Plus you can set the numformat to control whether group separators are shown. Whether these are commas, periods or something else depends upon your nls settings. Specifically the NLS_NUMERIC_CHARACTERS values:
SQL> set numformat "9G999G999G999G990"
SQL>
SQL> with vals as (
2 select 9 n from dual union all
3 select 9999 n from dual union all
4 select 9999999 n from dual union all
5 select 9999999999 n from dual
6 )
7 select * from vals;
N
------------------
9
9,999
9,999,999
9,999,999,999
SQL>
SQL> alter session set NLS_NUMERIC_CHARACTERS = ',.';
Session altered.
SQL>
SQL> with vals as (
2 select 9 n from dual union all
3 select 9999 n from dual union all
4 select 9999999 n from dual union all
5 select 9999999999 n from dual
6 )
7 select * from vals;
N
------------------
9
9.999
9.999.999
9.999.999.999
SQL>
SQL> alter session set NLS_NUMERIC_CHARACTERS = ',#';
Session altered.
SQL>
SQL> with vals as (
2 select 9 n from dual union all
3 select 9999 n from dual union all
4 select 9999999 n from dual union all
5 select 9999999999 n from dual
6 )
7 select * from vals;
N
------------------
9
9#999
9#999#999
9#999#999#999I don't know whether this is supported in Toad. If not you'll have to ask elsewhere to find out how to do this. Or hope someone will provide a follow up with this information :)