1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Adding numbers to a number field

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Dec 12, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    I have a number field with data ex: "12345678".
    Need to add two zeros like this. "1002345678".

    I have given example of just one row but need to add this zeros for all the columns in the table.

    Kindly suggest on this
     
  2. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    Hi, you can try

    :variable := substr:)variable,1,1)||'00'||substr:)variable,2,50)

    this is what you want?
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You show these 'numbers' as strings; are they actually numbers or are they in a varchar2 column? If they are actually numbers, are all the same length and start with 1 the easiest way is to simply add 990000000 to each value. If they are strings (which is not a good way to store actual numbers then the previous response is what you want. Of course if these ARE numbers and are of varying values with nothing but being numbers in common then you will need to use substr() on them, generate your new number then convert it back to a number:

    Code (SQL):
    UPDATE mytable
    SET mynum = to_number(substr(mynum,1,1)||'00'||substr(mynum, 2, LENGTH(mynum)-1));
    The above may be easier than computing the difference between the desired number and the existing value and adding.
     
  4. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Thank you for your reply. It worked now.