• Questions
• # Formatting numbers to group exponent by 3

Thanks for the question, Eddy.

Asked: February 21, 2024 - 9:09 am UTC

Last updated: February 26, 2024 - 5:47 am UTC

Version: 11.2

Viewed 1000+ times

Hi All
It's my first question here :)

I read about formatting number in scientific notation, but I don't find a way to group exponent by 3
For instance, I would have this result

Number -> result

1 -> 001.0E0
12 -> 012.0E0
123 -> 123.0E0
1234 -> 001.2E3
12345 -> 012.3E3
123456 -> 123.4E3
1234567 -> 001.2E6
...

is this a way to do this in a to_char function with the format clause ?
Regards
Eddy

## and Chris said...

There's not a built-in way that I'm aware of.

You can roll your own by:

- Use LOG base 10 to find the number of digits to the left of the decimal
- Convert this to multiples of three
- Divide the input number by 10^multiple
- Format this result and tack on the exponent

Here's an example of the logic in an inline PL/SQL function:

```with function fmt ( n number ) return varchar2 as
multiple integer;
formatted_number varchar2(10);
begin
multiple := floor ( log ( 10, n ) / 3 ) * 3;
return to_char ( n / power ( 10, multiple ), '009.9' ) || 'e' || multiple;
end;
rws as (
select level x,
to_number ( replace ( SYS_CONNECT_BY_PATH ( level, ',' ) , ',' ) ) n
from dual connect by level <= 7
)
select x, n
, fmt ( n )
, to_char ( n, '009.9eeee' )
, to_char ( n, 'tme' )
from   rws;

X     N          FMT(N)       TO_CHAR(N,'009.9EEEE')  TO_CHAR(N,'TME')    TO_CHAR(N,'TM9')
1          1  001.0e0      1.0E+00                 1E+00               1
2         12  012.0e0      1.2E+01                 1.2E+01             12
3        123  123.0e0      1.2E+02                 1.23E+02            123
4       1234  001.2e3      1.2E+03                 1.234E+03           1234
5      12345  012.3e3      1.2E+04                 1.2345E+04          12345
6     123456  123.5e3      1.2E+05                 1.23456E+05         123456
7    1234567  001.2e6      1.2E+06                 1.234567E+06        1234567   ```

## Rating

(1 rating)

### Great and brilliant !

Eddy, February 24, 2024 - 6:51 am UTC

Thanks Chris
That's exactely what I need
Eddy
February 26, 2024 - 5:47 am UTC