Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: Parsing in Oracle SQL or PL-SQL

    Problem from a friend - not sure version of Oracle, probably old:
    Oracle table with 1 column for First Name + Middle Initial (sometimes).

    As part of an export process, they want to separate it out into two columns. This will be done about once per month. Volume is low (in the hundreds) so performance is not a consideration.

    A "Middle Initial" can be defined as:
    1 character grouping at the end of the column value sometimes followed by a dot but always preceded by 1 or more spaces and 1 or more groupings of characters (presumably the First Name).

    What advice for methods for him to use? Thanks so much.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As it is an old Oracle version, I'd suggest you to use SUBSTR in combination with INSTR function. Here is a test case and sample code; see if it helps.

    First, several F1 drivers (I don't know their middle names, but never mind):
    Code:
    SQL> select * from fm;
    
    COL                  FIRST                MIDDLE
    -------------------- -------------------- ----------
    Alonso F.
    Raikkonen     K
    Coulthard D
    Schumacher
    Now update the table by distinguishing first and "middle" name:
    Code:
    UPDATE FM SET
      first = SUBSTR(col, 1, DECODE(INSTR(col, ' ', 1), 0, LENGTH(col),
                                                           INSTR(col, ' ', 1)
                                   )
                    ),
      middle = trim(SUBSTR(col, DECODE( INSTR(col, ' ', 1), 0, LENGTH(col) + 1,
                                                               INSTR(col, ' ', 1)
                                      )
                          )
                    );
    Finally, the result:
    Code:
    SQL> select * from fm;
    
    COL                  FIRST                MIDDLE
    -------------------- -------------------- ----------
    Alonso F.            Alonso               F.
    Raikkonen     K      Raikkonen            K
    Coulthard D          Coulthard            D
    Schumacher           Schumacher

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Thank you for the response! Much appreciated!

    I forwarded it on and I'll let you know if it works for him. He probably won't get to it for a week or so.

Posting Permissions

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