Format model

User_DIL60

mathguy

The "function" to "add two zeros" after an integer, and to "remove the decimal point" from a decimal number with two decimal places, is taught in middle school: it's multiplication by 100.

But you don't need to do that explicitly. Use the proper format model when you convert the number to string. Use a string of a zero followed by 11 nines to get the number left-padded with zeros to a length of 12. Use the V format model element to "multiply by 100" (really, to "move the decimal point"), and the MI format model element to show the minus sign as trailing. All of this is found easily in the documentation.

select input_numb, to_char(input_numb, '0999999999v99mi') as output
from   test;


INPUT_NUMB   OUTPUT      
----------   -------------
       123   000000012300 
    999.14   000000099914 
    -999.1   000000099910-

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16790863#Comment_16790863

Meaning?

That's pretty rude. To the two people you called out to (as if they have nothing better to do all day than to scan this forum to see if anyone is asking for help), and to everyone else (as if there are just those two people who know enough to be able to help you).

It used to be that the "forum rules" specifically warned forum participants against this specific behavior. Not sure if it's still there; perhaps it is. In any case, it also "used to be" that people would read those rules - and that they would care about them. Ugh.

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16790874#Comment_16790874

I don't understand. I explained how the "logic" works in my original answer - which part of it is difficult?

If you need a refresher on format models, the documentation is the first place to look. But I don't know what it would tell you that I didn't already explain. Anyway, if you need a link, here it is:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570

Solomon Yakobson

Again, it isn't clear what output you expect. If you don't want trailing space for non-negative numbers then use FM modifier:

with t as (
           select 100 n from dual union all
           select -100 from dual
          )
select  n,
        '[' || to_char(n,'0999999999v99mi') || ']' no_fm,
        '[' || to_char(n,'fm0999999999v99mi') || ']' fm
  from  t
/

         N NO_FM           FM
---------- --------------- ---------------
       100 [000000010000 ] [000000010000]
      -100 [000000010000-] [000000010000-]

SQL>

SY.

Solomon Yakobson

So my guess was right - use FM modifier.

SY.

Solomon Yakobson

It has length of N for non-negative number and N + 1 for negative numbers where N is number of zeroes/nines in format mask. So if you want N to be 11 then format should be 'FM099999999V99MI':

with t as (
           select 100 n from dual union all
           select -100 from dual
          )
select  n,
        to_char(n,'FM099999999V99MI') val,
        length(to_char(n,'FM099999999V99MI')) len
  from  t
/
         N VAL                 LEN
---------- ------------ ----------
       100 00000010000          11
      -100 00000010000-         12

SQL>

SY.