5 Replies Latest reply: Aug 22, 2014 10:08 AM by Sruthi Tamiri RSS

    mypriceformat feature clarification

    Sruthi Tamiri

      Hi Team,

       

        Could you help me on this scenario

       

      select ltrim(to_char('1877','999G990D99')) x from dual

      union all

      select ltrim(to_char('1876','fm999G990D9999')) x from dual;

       

      The out will appear as

       

      1,877.00 for format 999G990D99

      1,876. for format fm999G990D9999

       

      As currently we are updated fm format, how we can ignore period operator in end, if the value entered as numeric only.

       

      Regards,

      Sruthitamiri

        • 1. Re: mypriceformat feature clarification
          Sruthi Tamiri

          Hi All,

           

            Any help on this request please!!

           

          Regards,

          Sruthitamiri

          • 2. Re: mypriceformat feature clarification
            fac586

            Sruthi Tamiri wrote:

             

              Could you help me on this scenario

             

            select ltrim(to_char('1877','999G990D99')) x from dual

            union all

            select ltrim(to_char('1876','fm999G990D9999')) x from dual;

             

            The out will appear as

             

            1,877.00 for format 999G990D99

            1,876. for format fm999G990D9999

             

            As currently we are updated fm format, how we can ignore period operator in end, if the value entered as numeric only.

            Where and why do you want to do this? In most circumstances the best practice is to keep the data in its native type (in this case as NUMBERs) and format it consistently on final display.

             

            What you are requesting can't be done using only the conversion format mask. If you must, you have to trim any trailing decimal character off using an additional function:

             

            > with t as (

                select 1876 x from dual

                union all

                select 187.6 from dual

                union all

                select 1.876 from dual

                union all

                select 0.1876 x from dual

                union all

                select 0.01876 x from dual

                union all

                select 0.187600 x from dual

                union all

                select -0.018760 x from dual

                union all

                select 0 x from dual)

            select to_char(x,'999G990D99') y, rtrim(to_char(x,'fm999G990D9999'), '.,') z from t;

             

            Y          Z        

            ----------- -------------

              1,876.00 1,876      

                187.60 187.6      

                  1.88 1.876      

                  0.19 0.1876      

                  0.02 0.0188      

                  0.19 0.1876      

                 -0.02 -0.0188    

                  0.00 0

            • 3. Re: mypriceformat feature clarification
              Sruthi Tamiri

              Hi face586,

               

                Thanks for your suggestion,  i am trying to check in different scenarios like as shown below, but some where i am missing out, in one of existing process i need to pass additional parameter 

               

              select  rtrim(to_char('0.354','fm999G990D9999') ||' '||'USD/bbl') from dual

              union all

              select  rtrim(to_char('0.354','fm999G990D9999'),'.,' ||' '||'USD/bbl') from dual

               

              The output should return as  0.354 USD/bbl but 0.354

               

              Regards,

              Sruthitamiri

              • 4. Re: Re: mypriceformat feature clarification
                fac586

                Sruthi Tamiri wrote:

                 

                Hi face586,

                 

                  Thanks for your suggestion,  i am trying to check in different scenarios like as shown below, but some where i am missing out, in one of existing process i need to pass additional parameter

                 

                select  rtrim(to_char('0.354','fm999G990D9999') ||' '||'USD/bbl') from dual

                union all

                select  rtrim(to_char('0.354','fm999G990D9999'),'.,' ||' '||'USD/bbl') from dual

                 

                The output should return as  0.354 USD/bbl but 0.354

                Put the parentheses in the right place:

                 

                > select to_char(0.354, 'fm999G990D9999') || ' USD/bbl' from dual

                union all

                select rtrim(to_char(0.354, 'fm999G990D9999'), '.,') || ' USD/bbl' from dual;

                 

                TO_CHAR(0.354,'FM999G990D9999')||'USD/BBL'

                ------------------------------------------

                0.354 USD/bbl                            

                0.354 USD/bbl                           

                 

                Your code is adding ' ', U, S, D, /, b and l to the set of characters to be trimmed from the right of the string.

                 

                Why are you passing '0.354' to the TO_CHAR function? '0.354' is already a character string. It doesn't need conversion. This form of the TO_CHAR function takes a number parameter, so use 0.354 without quotes.

                • 5. Re: mypriceformat feature clarification
                  Sruthi Tamiri

                  Thanks for work around to resolve the problem...Many thanks.

                   

                  Regards,

                  Sruthitamiri