Results 1 to 2 of 2

Thread: parse a column

  1. #1
    Join Date
    Mar 2004

    Unhappy Unanswered: parse a column


    I would appreciate help on the following.
    I have a column in a table. Each entry in the column can
    have 0,1,2,3... maybe four entries in it.

    I would like to parse the column and grab each of the entries and
    feed it into another select statement. This entry that needs
    to be parsed would be found through a select statement.
    I would like to know what a good way is for doing this.

    For example an entry in the column may be
    AI732541 AI791498 AA508861
    another entry may be

    Basically, in the first example
    AI732541 AI791498 AA508861 would be the the answer ie. access_num for my first
    select statement
    Select access_num from inventory where prod_id = current_prod_id;

    I would like to parse the entry
    to allow me to access AI732541 to feed into another select statement
    find out the answer and capture in a variable.
    Then I would like to be able to access the next entry AI791498 and
    perform the same task of feeding it into another select statement
    and capturing the answer in a variable an so on.

    I am not sure what the syntax is if I use a while loop. I don't think
    that I need to involve a perl yet as I am only retrieving one row and
    want to process within the row and not several rows.

    thanks in advance,

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    While there are ways to solve this problem using SQL, they are ugly. This table violates 1NF (the first normal form), and the only practical way to fix that is with some kind of iterative loop. I don't know of any JOIN or standard function that will fix it.

    Write a short Perl script to decompose this column (probably using the split() function), and rewrite the table with only one access_num per row.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts