Forum Stats

  • 3,768,996 Users
  • 2,252,894 Discussions
  • 7,874,831 Comments

Discussions

Format model

User_DIL60
User_DIL60 Member Posts: 20 Green Ribbon
edited Apr 6, 2021 7:28PM in SQL & PL/SQL

Comments

  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond

    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
    mathguy Member Posts: 10,155 Blue Diamond

    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
    mathguy Member Posts: 10,155 Blue Diamond

    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
    Solomon Yakobson Member Posts: 18,914 Red Diamond

    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
    Solomon Yakobson Member Posts: 18,914 Red Diamond

    So my guess was right - use FM modifier.

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,914 Red Diamond

    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.

    User_DIL60