14 Replies Latest reply on Jun 12, 2018 5:41 AM by KayK

    Preserve zero before decimal point

    Marco Foxx

      Hello,

      Oracle 11g r2, database column's datatype is number in which i am trying to store numbers.

       

      My scenario,

      i have different values ranging from +(positive) to - (negative) (in between +99.99 to -99.99(fix)),when i have + value i need to convert that + into 1 and if i have - then convert - into 0.

       

      Desire output :

      if value is...

      +11.25 then 11125,(where initial 1 is for +)

      +0.50 then 1050,

      +0.20 then 1020,

      -10.20 then 01020,(where initial 0 is for -)

      -0.40 then 0040 and so on...

      I have tried below way possible but when it comes to +0.20 or -0.40(in short leading zero) then output becomes like : 120 and 040 respectively which is wrong the correct output should be 1020 and 0040 respectively.

      SELECT 006
             || 
                   TRIM (
                      DECODE (
                         SIGN (ABS (+0.25)),
                         -1, 0
                             || TRIM (
                                   REGEXP_REPLACE (ABS (+0.25),
                                                   '[^[:alnum:]'' '']',
                                                   '')),
                         1, 1
                            || TRIM (
                                  REGEXP_REPLACE (ABS (+0.25),
                                                  '[^[:alnum:]'' '']',
                                                  '')),
                         0
                         || TRIM (
                               REGEXP_REPLACE (ABS (+0.25),
                                               '[^[:alnum:]'' '']',
                                               ''))))
        FROM DUAL
      

      Can someone guide how to preserve leading zero value.

        • 1. Re: Preserve zero before decimal point
          cormaco

          You can preserve leading zeros by using a format model with TO_CHAR e.g FM00000.

           

          This is close but not exactly your requirement:

          WITH INPUT(VALUE) AS (
              SELECT +11.25 FROM dual UNION ALL
              SELECT  +0.50 FROM dual UNION ALL
              SELECT  +0.20 FROM dual UNION ALL
              SELECT -10.20 FROM dual UNION ALL
              SELECT -0.40 FROM dual
          )
          SELECT 
          TO_CHAR(
              CASE 
                  WHEN VALUE >=  10 THEN VALUE + 100
                  WHEN VALUE >    0 THEN VALUE +  10
                  WHEN VALUE <    0 THEN -VALUE
                  ELSE
                      VALUE
              END * 100,
              'FM00000') new_value
          FROM INPUT
          
          NEW_VALUE
          ---------
          11125    
          01050    
          01020    
          01020    
          00040    
          
          • 2. Re: Preserve zero before decimal point
            KayK

            Hi Marco,

            sorry, i don't understand what you want to achieve. I expect some kind of sorting, right ?

             

            Perhaps this old-fashioned statement helps a little:

             

            with intest as (
            select '+11.25' t1 from dual union
            select '+0.50'  t1 from dual union
            select '+0.20'  t1 from dual union
            select '-10.20' t1 from dual union
            select '-0.40'  t1 from dual ),
            test as (select t1, to_char(t1, '00d00') t2 from intest)
            select t1, t2, decode(substr(t2,1,1), '-', '0', '1')|| replace(substr(t2,2,99), '.', '') from test;
            
            T1      T2    DECOD
            ------- ----- -----
            +0.20   00.20 10020
            +0.50   00.50 10050
            +11.25  11.25 11125
            -0.40  -00.40 00040
            -10.20 -10.20 01020
            
            

             

            regrads
            Kay

            1 person found this helpful
            • 3. Re: Preserve zero before decimal point
              Marco Foxx

              Okay so i have narrow it down to one value to i can understand it better..

              below query returns the correct result.

               

              with intest as ( 

              select '+0.20'  t1 from dual    ), 

              test as (select t1, to_char(t1, '00d00') t2 from intest) 

              select t1,  decode(substr(t1,1,1), '-', '0', '1')|| replace(substr(t1,2,99), '.', '') from test;

               

              output :

              t1               t2

              +0.20         1020

               

              when i use..

               

              select 

              decode(substr(+0.25,1,1), '-', '0', '1')  || replace(substr(+0.20,2,99), '.', '')

                         from dual;

               

              it gave me wrong output

              12

               

              If you can please elaborate.

              • 4. Re: Preserve zero before decimal point
                KayK

                my second substr uses my t2 column, that is the result from  to_char(t1, '00d00').

                 

                In your example you have to use ' around the numeric-strings to avoid an implicite conversion to number.

                This will work better for this particular example but not for all of your values:

                 

                < scott:op57 > select decode(substr('+0.20',1,1), '-', '0', '1')  || replace(substr('+0.20',2,99), '.', '')  from dual;
                
                DECO
                ----
                1020
                
                • 5. Re: Preserve zero before decimal point
                  Marco Foxx

                  After posting the comment i released that and change the code as above.. now if i insert those records into table with number datatype it removes leading zeros and idea about that !! create table temp_data_collect (temp_col number); / insert into temp_data_collect  (temp_col ) select decode(substr( '-0.20',1,1), '-', '0', '1')|| replace(substr( '-0.20',2,99), '.', '') from dual

                  • 6. Re: Preserve zero before decimal point
                    Marwim

                    Numbers don't have leading zeroes.

                    If you want to preserve them you have to store them a strings.

                     

                    Marcus

                    • 7. Re: Preserve zero before decimal point
                      KayK

                      That's a part if my missing understanding what you want to achieve.

                       

                      A column with datatype NUMBER has no leading zeros, it can't have.

                       

                      You can bring back leading zeros to the output with an appropriate format-mask.

                      Or you have to store your numeric content as CHAR or VARCHAR, but that causes more and other problems

                      • 8. Re: Preserve zero before decimal point
                        John Thorton

                        Marco Foxx wrote:

                         

                        After posting the comment i released that and change the code as above.. now if i insert those records into table with number datatype it removes leading zeros and idea about that !! create table temp_data_collect (temp_col number); / insert into temp_data_collect (temp_col ) select decode(substr( '-0.20',1,1), '-', '0', '1')|| replace(substr( '-0.20',2,99), '.', '') from dual

                        You do NOT have a data storage issue.

                        You have a data presentation issue.

                        • 9. Re: Preserve zero before decimal point
                          ascheffer

                          WITH intest AS ( 

                              SELECT +11.25 t1 FROM dual UNION ALL 

                              SELECT  +0.50 FROM dual UNION ALL 

                              SELECT  +0.20 FROM dual UNION ALL 

                              SELECT -10.20 FROM dual UNION ALL 

                              SELECT 0 FROM dual UNION ALL 

                              SELECT -0.40 FROM dual 

                          )

                          select t1, translate( to_char( t1, '00.00' ), '- .','01' )

                          from intest

                          1 person found this helpful
                          • 10. Re: Preserve zero before decimal point
                            BluShadow

                            John Thorton wrote:

                             

                            Marco Foxx wrote:

                             

                            After posting the comment i released that and change the code as above.. now if i insert those records into table with number datatype it removes leading zeros and idea about that !! create table temp_data_collect (temp_col number); / insert into temp_data_collect (temp_col ) select decode(substr( '-0.20',1,1), '-', '0', '1')|| replace(substr( '-0.20',2,99), '.', '') from dual

                            You do NOT have a data storage issue.

                            You have a data presentation issue.

                             

                             

                             

                             

                            Yes!  +1

                             

                            That's why we have a community document on this very topic...

                             

                            PL/SQL 101 : DataTypes - NUMBER

                             

                            For which the first question it answers is "How do I store numbers with leading zeros?"   

                            1 person found this helpful
                            • 11. Re: Preserve zero before decimal point
                              Solomon Yakobson

                              TO_CHAR converts ether number or date to a string. When you use to_char(t1, '00d00') Oracle checks t1 datatype and realizes it is a string. So it implicitly converts t1 to number and passes that number to TO_CHAR which converts it to '+0.20' according to second parameter '00d00'. Expresiion decode(substr(t1,1,1), '-', '0', '1') returns 0 if t1 first character is 0 otherwise it returns 1. Since t1 first character is plus, we get 1. Expression replace(substr(t1,2,99), '.', '')  select part of t1 starting from second character resulting in '0.20' and replaces dot with null (removes dot) resulting in '020' So final result is '1020'. And while in your first example t1 is string, in your second example value passed to substr is number and since substr first parameter type is string/clob Oracle implicitly converts number to string. As a result number +0.25 becomes string '.25' and +0.20 becomes string '.2' so you end up getting '12'

                               

                              SY.

                              • 12. Re: Preserve zero before decimal point

                                Can someone guide how to preserve leading zero value.

                                You just told us above that you no longer have ANY leading zeroes because you are trying to use zero  as a minus indicator.

                                 

                                Oracle already has a minus indicator.

                                 

                                Just store the numeric value in the NUMBER datatype and use a view and TO_CHAR to display it in the format you want.

                                 

                                You generally should NOT alter your raw data in a way that causes you to lose the actual value or to lose scale or precision.

                                • 13. Re: Preserve zero before decimal point
                                  Marco Foxx

                                  Thank you for great responses :)

                                  • 14. Re: Preserve zero before decimal point
                                    KayK

                                    Hi Marco,

                                    thanks for the 100 points, but my posting isn't the correct answer to your question.

                                     

                                    It's some kind of workaround for your requirement from above.

                                    Please keep in mind all the advises given here that a number is a number and has to be stored as column with datatype NUMBER.

                                     

                                    The presentation to the user is a totally different issue. Take a step back and reconsider your requirements.

                                     

                                    kind regards
                                    Kay