Number to char conversion doesnt work with leading 0

Question

I have a case to convert the numbers to character.

But the 0 are missing when i try to convert them to char. So i tried to convert with the following method.

select rtrim(to_char(0.5, 'FM90.099'), '.') from dual;

But this doesnt work with 00.500. 00.500 also comes as 0.50 and 110.50 comes as #######. to make this case work i need to change this as to_char(110.50,'FM990.099). but again it wont work if there is a 4 digit number. I am also not sure how many digits can the production system have. is there any other to convert number to char with which the 0's doesnt miss.

Answer 1

Put in sufficient leading and trailing 9s to account for your maximum precision and scale. If the format mask is not big enough then increase the number of 9s until it is.

SELECT RTRIM(TO_CHAR(value, 'FM999999999999999999990.99999999'), '.') AS formatted_number
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
select 0.5     from dual UNION ALL
select 0.5000  from dual UNION ALL
select 0000.5  from dual UNION ALL
select 000.500 from dual UNION ALL
select 1e10    from dual UNION ALL
select 110.1234567 from dual;

Outputs:

FORMATTED_NUMBER
0.5
0.5
0.5
0.5
10000000000
110.1234567

db<>fiddle here