13 Replies Latest reply: Sep 10, 2013 9:59 AM by Paul Horth RSS

    Append 0's infront of number

    Thej


      Hi Experts,

       

      I have a table which containd 10 column.

       

      One of the column contains numbers.

       

      Now I have to append "0" s infront of these no's for all rows in a table .

       

      can some one help me out.

       

      sample column data as below

       

      MSIDNO

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

      817600810

      614201429

      815795262

      614019116

      817121742

      813167782

      815122985

      614152719

      767433893

      614323233

      814582410

      814850162

      815986700

      817304479

        • 1. Re: Append 0's infront of number
          Rahul_India
          WITH T AS
          (
          SELECT 817600810 AS NUM FROM DUAL UNION ALL
          SELECT  614201429 AS NUM FROM DUAL
          )
          SELECT 0||NUM FROM T
          
          • 2. Re: Append 0's infront of number
            Frank Kulash

            Hi,

             

            If you want to pad the numbers so that they all contain, say, 10 digits (adding leading 0's if they are not already 10 digits), then

             

            TO_CHAR (msidno, '0000000000')

             

             

            If you want to add a leading 0, regardless of the number of digits:

             

            '0' || TO_CHAR (msidno)

            Either way, given that  msidno is a NUMBER (an integer) the result will be a VARCHAR2.

             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements) , and the results you want from that data.  Make sure the sample data shows all the special cases you might need to handle (for example, different numbers of digits).
            Explain, using specific examples, how you get those results from that data.
            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

            See the forum FAQ: https://forums.oracle.com/message/9362002

            • 3. Re: Append 0's infront of number
              Purvesh K

              Do you really think appending 0's to the numbers will be stored in the same way? how many times have you appended the numbers with 0 when writing a Cheque? If you have not, then why do you think should oracle do it?

               

              Irrespective of whether you precede a number with a 0, if the column data type is Number/Numeric, it will be stripped off, because it is insignificant to store leading zeroes.

               

              If you want to display the numbers with a leading Zero, then use the way Rahul has shown in previous post. Another way to do so is using TO_CHAR with Fm Format model.

               

              see an example:

              with data as

              (

                select 10000 col from dual union all

                select 999 col from dual union all

                select 9 col from dual

              )

              select col, to_char(col, 'fm000000') format_col

                from data;

               

              COL                    FORMAT_COL

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

              10000                  010000   

              999                    000999   

              9                      000009

              • 4. Re: Append 0's infront of number
                Thej

                Thanks Frankkulash,

                 

                ANS:

                update table_name  set msidno='0'|| TO_CHAR (msidno);

                • 5. Re: Append 0's infront of number
                  Pablolee

                  Thej, you have marked your own response as being the correct one, surely you should have marked one of the respondents' posts as being the correct answer...

                  • 6. Re: Append 0's infront of number
                    kendenny

                    No that's not the correct answer. Do that and then query the table. You will see that nothing changed. The column is a number. It is stored as a number. Your update statement will not cause any change in how it's stored because the number 123 has the exact same value as the number 0123. You can't store the leading zero in the database. You have to add the leading zero when you display the number to a human which means any applications which access the data need to make the change.

                    • 7. Re: Append 0's infront of number
                      Purvesh K

                      Though marking own post as correct, will not give OP the points

                      • 8. Re: Append 0's infront of number
                        Thej

                        I have not told clumn is of number type its a varchar2 type contains numbers as a values .. so as per  Frankkulash suggestion the update statement works fine.

                        • 9. Re: Append 0's infront of number
                          kendenny

                          In that case you don't need th to_char. I thought to_char would give you an error when you gave it a varchar2 but evidently it doesn't.

                          • 10. Re: Append 0's infront of number
                            EdStevens

                            Thej wrote:

                             

                            I have not told clumn is of number type its a varchar2 type contains numbers as a values .. so as per  Frankkulash suggestion the update statement works fine.

                            The fact that your column is not a number but a varchar2 would should have been stated up front, instead of making us guess.

                            The fact that your column is a varchar means it is not and never was a number.  It is simply a string of numeric characters.

                            • 11. Re: Append 0's infront of number
                              Paul  Horth

                              kendenny wrote:

                               

                              In that case you don't need th to_char. I thought to_char would give you an error when you gave it a varchar2 but evidently it doesn't.

                              Oracle kindly converts it to a number for you first, then it applies the to_char to that number.

                              • 12. Re: Append 0's infront of number
                                kendenny

                                PaulHorth wrote:

                                 

                                kendenny wrote:

                                 

                                In that case you don't need th to_char. I thought to_char would give you an error when you gave it a varchar2 but evidently it doesn't.

                                Oracle kindly converts it to a number for you first, then it applies the to_char to that number.

                                It appears that it doesn't. Otherwise this would give me an error

                                SQL> select to_char('abc') from dual;

                                 

                                TO_
                                ---
                                abc

                                • 13. Re: Append 0's infront of number
                                  Paul  Horth

                                  You're absolutely right - I'm obviously in need of a coffee!

                                  to_char is happy to accept character parameters.