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!

inserting comma seperated values as different values for the column

Discussion in 'SQL PL/SQL' started by basavraj, Sep 15, 2009.

  1. basavraj

    basavraj Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    hi all,
    i am new to the SQL PL/SQL world.
    i am writing a procedure where the task is to take the comma seperated values and insert them into the table as seperate values.
    ex:the data will be like this

    office extension
    ----- -----------
    bangalore 299,301,145

    i should enter the above data in the table as below

    office extension
    ------ ----------
    bangalore 299
    bangalore 301
    bangalore 145

    thanks in advance,
    basavraj
     
  2. Shekhar81

    Shekhar81 Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    You need to parse the column extension in table 1 and look for commas. You can use the function INSTR in a loop to determine the position of comma and use substr to extract the number.
     
  3. basavraj

    basavraj Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    can u provide me a bit more clear explanation??
    i understand the use of INSTR and SUBSTR but i need to replicate the same data for the column 'office'. i tried using the curcor for the same, but that does not seem to b working..