11 Replies Latest reply: Sep 5, 2014 6:48 AM by Etbin RSS

    How to change number output that uses dot [.] to coma [,]

    jerry44

      I've function that should change notation of numbers from 12345.67€ to 12345,67€

       

          select

       

              replace(

          to_char(round(

       

          123456.789

       

          ,2),'999G999G999G990D'||substr('0000000000',1,2),'nls_numeric_characters=''.,'''),'.',','

          ) || ' €'

       

          from dual;

       

      current result is:

      123,456,79 €

      the result I want to get:

      123456,79 €

       

      But I' dont know how. My oracle returns values in format 123456.79 so this is a reason of my calculation. Doe's anybody know what to do? maybe there is possibility to change returned format of number without convertion in functions? It should be possible but I don't know how to do it

        • 1. Re: How to change number output that uses dot [.] to coma [,]
          Biju Das
          SQL> alter session set nls_numeric_characters=', ';
          
          Session altered.
          
          SQL> SELECT TO_CHAR(12345 / 15, '999g999d999') COL1,
            2  TO_CHAR(12345 /15, '999999d999') COL2
            3  FROM DUAL;
          
          COL1         COL2
          ------------ -----------
               823,000     823,000
          
          • 2. Re: How to change number output that uses dot [.] to coma [,]
            Sven W.

            Just switch the numeric_char semantic. You already have it in your code. Or switch the nls settings to german.

             

            Example

             

            select

                to_char(round(123456.789,2)

                      ,'FM999G999G999G990D00'

                      ,'nls_numeric_characters=.,'

                      )

                || ' €'

            from dual;

             

            123,456.79 €

             

             

            select

                to_char(round(123456.789,2)

                      ,'FM999G999G999G990D00'

                      ,'nls_numeric_characters=,.'

                      )

                || ' €'

            from dual;

             

            123.456,79 €


            NLS_NUMERIC_CHARACTERS=,. means: replace the Decimal separator (=D) with the firstvalue (=,) and the Group separator (=G) with the second value (=.)

            • 3. Re: How to change number output that uses dot [.] to coma [,]
              Frank Kulash

              Hi,

               

              So, you want comma to be the decimal marker.  In that case, make comma the first of the NLS_NUMERIC_CHARACTERs, like this:

              SELECT  TO_CHAR ( sal

                              , '999999999990D00'

                              , 'NLS_NUMERIC_CHARACTERS='', '''

                              )   AS f_sal

              FROM    scott.emp

              ;

              Since I don't have a test version of your table on my system, I used scott.emp, which is probably on your system.

              Output:

              F_SAL

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

                        800,00

                       1600,00

                       1250,00

                       2975,00

                       1250,00

                       2850,00

                       2450,00

                       3000,00

                       5000,00

                       1500,00

                       1100,00

                        950,00

                       3000,00

                       1300,00

               

              TO_CHAR automatically rounds the value to fit the format, so, unless you're doing something very special, you don't need to call both.

              • 4. Re: How to change number output that uses dot [.] to coma [,]
                jerry44

                There is no possibility to change the oracle displays in general? Function level takes more time to implement. i just need to change the way apex shows numbers in my app

                • 5. Re: How to change number output that uses dot [.] to coma [,]
                  Sven W.

                  What also works is to use correct language settings. NLS_LANG or the appropriate part of NLS_LANG, which would be NLS_TERRITORY.

                   

                  Example

                   

                  alter session set nls_territory='AMERICA';

                  session SET geändert.

                   

                  select to_char(123456.789,'FM999G999G999G990D00') || ' €' from dual;

                  TO_CHAR(123456.789,'FM999G999G999G990D00')||'€'

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

                  123,456.79 €                                  

                   

                  alter session set nls_territory='GERMANY';

                  session SET geändert.


                  select to_char(123456.789,'FM999G999G999G990D00') || ' €' from dual;

                  TO_CHAR(123456.789,'FM999G999G999G990D00')||'€'

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

                  123.456,79 €   

                   

                  This language settings can also be changed in the application settings in Apex. Usually apex derives it from the browser language.

                  But this would be a question for the apex forum.

                   

                  But since I want to test how images can be included into the forum here we go:

                   

                   

                  Copy&Paste from the snipping tool. Works like a charm!

                  • 6. Re: How to change number output that uses dot [.] to coma [,]
                    Etbin

                    Maybe  Format Models

                     

                    select n,

                           to_char(n,'999G999D99U','nls_numeric_characters = '',.'' nls_dual_currency = ''€''') x

                      from (select 123456.789 n

                              from dual

                           )

                     

                    NX
                    123456.789123.456,79€

                     

                    Regards

                     

                    Etbin

                    • 7. Re: Re: How to change number output that uses dot [.] to coma [,]
                      Biju Das

                      Hi Etbin,

                       

                      SQL>  select n,
                        2         to_char(n,'999G999D99U','nls_numeric_characters = '',.'' nls_dual_currency = ''€''') x
                        3    from (select 123456.789 n
                        4            from dual
                        5         );
                      
                               N X
                      ---------- ---------------------
                      123456,789           123.456,79€
                      
                      
                      
                      

                      In that case, 8 will be missed out from the decimal part.

                       

                      Regards

                      Biju

                      • 8. Re: Re: Re: How to change number output that uses dot [.] to coma [,]
                        Etbin

                        8 is not missing from the decimal part but rounded to 9 (because of the following 9) as stated in the link provided: All number format models cause the number to be rounded to the specified number of significant digits.

                        It's normal practice to specify only two decimal places - cents - when specifying the currency symbol too.

                         

                        Regards

                         

                        Etbin

                        • 9. Re: How to change number output that uses dot [.] to coma [,]
                          Biju Das

                          Shameless me!!! I should have observe it correctly. Thanks for clarifying Etbin.

                           

                          Regards

                          Biju

                          • 10. Re: How to change number output that uses dot [.] to coma [,]
                            jerry44

                            The result I want to get is the number to put within table on invoice. Decimals on the right of coma, should be 2 position even the number passed to function is without coma f.e. 3 - it should look like price 3,00€ ,if passed is 3.33333 should be 3,34€. Comma only for decimals. Value is passed from SQL query to display only item in APEX applitation.

                            • 11. Re: Re: How to change number output that uses dot [.] to coma [,]
                              Etbin

                              select n,

                                     ceil(100 * n) / 100 to_store_n_as_number,

                                     to_char(ceil(100 * n) / 100,'999G999D99U','nls_numeric_characters = '',.'' nls_dual_currency = ''€''') to_display_n

                                from (select 3 n from dual union all

                                      select 3.33333 from dual

                                     )

                               

                              NTO_STORE_N_AS_NUMBERTO_DISPLAY_N
                              333,00€
                              3.333333.343,34€


                              Regards

                               

                              Etbin