12 Replies Latest reply on Dec 6, 2017 1:54 PM by BluShadow

    trunc without to_char

    ora_1978

      select trunc(0.83333333,2) from dual;

      o/p:

      0.83

      expected :

       

      .83

      without to_char

        • 1. Re: trunc without to_char
          user477000

          Clearly you're interested in changing display format for decimals

          what tool do you use (SQlPlus/Toad/SQLDeveloper) ??

          • 2. Re: trunc without to_char
            BrunoVroman

            Hello,

             

              .83 is rather the default (personally I don't like it and I prefer 0.83)... Check your session NUMFORMAT:

            SQL> SHOW NUMFORMAT

             

            Probably something with  "0."? Try to unset it:

            SQL> SET NUMFORMAT ""

            (two double quotes)

             

            You can also try something like

            SQL> SET NUMFORMAT 999.99

            SQL> SELECT 0.833333 FROM dual;

            (remark: I havent' used TRUNC; in fact Oracle has ROUNDED as you can see if you try SELECT 0.8555 FROM dual;)

             

            Best regards,

             

            Bruno Vroman.

            Edited: I assumed SQL*Plus (what else? ;-)

            • 3. Re: trunc without to_char
              Manik

              Question : What problem are you actually trying to solve by removing the 0 before the decimal?

               

              Cheers,

              Manik.

              • 4. Re: trunc without to_char
                BluShadow

                ora_1978 wrote:

                 

                select trunc(0.83333333,2) from dual;

                o/p:

                0.83

                expected :

                 

                .83

                without to_char

                 

                As already mentioned, the issue you are referring to is a "display" issue, as the number 0.83 is exactly the same as the number .83, but are just displayed differently.

                How numbers are displayed depends on the interface you are using.  e.g. if I use my default SQL*Plus session I get...

                 

                SQL> select trunc(0.83333333,2) from dual;

                TRUNC(0.83333333,2)
                -------------------
                                .83

                 

                Yet if I run the same query in TOAD, the result shows me 0.83

                 

                As Bruno indicates, in an interface like SQL*Plus, you can change the NUMFORMAT setting to get what you want.  In other interfaces, they likely have their own settings somewhere that you can change to indicate how you want numbers to be represented.

                However, if you always want it to be represented a certain way, regardless of the interface, then TO_CHAR, included in the select statement is the method to do that.  TO_CHAR is the way to say that you want a value displayed in a certain way from the query, though of course it does mean that your query is returning a VARCHAR2 string rather than a number (or date if you're dealing with dates).

                Commonly, display formatting is left to the interface, especially when generating reports.  So the reporting tool, or the specific report, will be set up to display the values in the way the end user wants, and in some cases, the end user can change it for themselves.

                • 5. Re: trunc without to_char
                  ora_1978

                  if the value is 111.99999 then it should print 111.99

                   

                  Should be a generic solution

                  • 6. Re: trunc without to_char
                    ora_1978

                    without to_char

                     

                     

                    create table test_number_type(c1 number(5,2));

                     

                     

                    insert into test_number_type values(100.12);

                     

                     

                    insert into test_number_type values(0.12);

                     

                     

                    commit;

                     

                     

                    select trunc(c1,1) from test_number_type;

                     

                     

                    o/p:

                     

                     

                    100.1

                    0.1

                     

                     

                    expected 100.1

                    .1

                    • 7. Re: trunc without to_char
                      BrunoVroman

                      Hello,

                       

                      when you have 0.83 you expect .83 and when you have 0.1 you expect .1?

                      I am afraid nobody can help you ;-)

                       

                      Regards,

                       

                      Bruno

                      • 8. Re: trunc without to_char
                        ora_1978

                        create table test_number_type(c1 number);

                         

                         

                        insert into test_number_type values(100.12);

                         

                         

                        insert into test_number_type values(0.12);

                         

                         

                        commit;

                         

                         

                         

                         

                        create or replace view  test_data_type as

                        select to_number(trim(leading 0 from c1)) c_num_check from test_number_type;

                         

                         

                        desc test_data_type

                         

                         

                        when i select from view the result again changed because of the number data type.

                        • 9. Re: trunc without to_char
                          BluShadow

                          There's no such thing as a "generic solution"

                          Which part of "different interfaces give different results" are you not understanding?  Which part of "TO_CHAR is the way to say that you want a value displayed in a certain way from the query" do you not understand?

                           

                          When you have a non-string datatype, then it is entirely up to the interface to decide how to interpret that data and display it as a string.  That interface can be influenced by settings, but those settings are usually specific to that interface, so there is NO "generic" solution that will work in any interface.  The ONLY way to guarantee the display of a non-string value in a particular string-like format is to actually convert it to a string yourself using TO_CHAR.

                           

                          Is that clear enough?

                          • 10. Re: trunc without to_char
                            Paulzip

                            0.83 = .83  They are exactly the same.

                             

                            You are displaying a number, not a string, so it's up to OS and / or the interface of the application in question that is displaying the data, to determine if it bothers displaying a leading zero or not.  If you want to ensure it shows a leading zero (or not), your choices are simple and limited:

                            1. See if there is a global OS setting for this
                            2. See if there is an application setting for this.
                            3. Format the data as a string, where YOU control it.
                            • 11. Re: trunc without to_char
                              mathguy

                              The question makes no sense. I am afraid we are going down another rabbit hole - just like your question from yesterday:

                               

                              Date format - WITHOUT NLS_DATE    -  which you then posted in a DIFFERENT VERSION here:

                              https://stackoverflow.com/questions/47657199/date-format-insert-other-than-nls-date-format?noredirect=1#comment82274338_…

                               

                              as if you hadn't already received sufficient answers on this Forum.

                               

                              I won't even try to answer the question in THIS thread (you are asking essentially the same question as yesterday, but for numbers instead of dates). I see it as a waste of time.

                               

                              Then, you may ask, why did I even bother to post anything in this thread. It is to alert the other members to your approach to these problems; perhaps they will choose to stop replying, given this information.

                               

                              ADDED:   Sure enough

                               

                              https://stackoverflow.com/questions/47669523/trim-function-without-to-char

                              https://stackoverflow.com/questions/47672361/leading-zeroes-not-working-with-number-data-type

                              • 12. Re: trunc without to_char
                                BluShadow

                                And as people are already starting to reply on SO.... the answer is to use TO_CHAR.

                                 

                                @ora-1978  You need to understand that numbers are stored on the database in an internal format, and you are talking about a display issue, not a storage issue.

                                No surprises, we've seen people ask this over and over again on this community, hence like the DATE datatype question you had yesterday, there is also a community document for the NUMBER datatype...  PL/SQL 101 : DataTypes - NUMBER

                                 

                                Read it and learn.