CODEWITHSUNDEEP
oracle-databaseplsql
Thomas Carlton
Thomas Carlton

Reputation: 5968

How to format number in PL/SQL?

I need to convert some numbers to chars according to the following logic :

Input     => Expected Output  |   Current Output 
0         => 0                |   0.00  << Wrong
.1111     => 0.11             |   0.11
.1        => 0.1              |   0.10  << Wrong
1.111     => 1.11             |   1.11

Basically my logic is to have the minimum of characters. Only the user friendly caracters that describe the number.

Here is my current function

to_char(Value,'9999999999999990D99');

As you can see for 0 for example, it returns 0.00

Does anyone know how to solve that please ?

Thanks.

Upvotes: 1

Views: 4770

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Looks like you want this one:

rtrim(to_char(Value,'fm99999999999990D99'),'.')

Ie, you need to add 'fm' in format mask and them remove '.': Example:

select 
  to_char(Value,'9999999999999990D99') xx 
 ,to_char(Value,'fm9999999999999990D99') x_fm  -- just FM
 ,rtrim(to_char(Value,'fm99999999999990D99'),'.') x_fm_trim -- FM + rtrim
from xmltable('0, 0.1111, 0.1, 1.111' columns value number path '.');

XX                   X_FM                 X_FM_TRIM
-------------------- -------------------- ------------------
                0.00 0.                   0
                0.11 0.11                 0.11
                0.10 0.1                  0.1
                1.11 1.11                 1.11

Upvotes: 3

Related Questions