Results 1 to 4 of 4

Thread: SQL Help

  1. #1
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130

    Unanswered: SQL Help

    Hi,
    I have a table and one of the column is name with CHAR(25). The name is first name followed by Middle name and last name with comma separated and suffix at the end if there any. Not all the names have middle name or suffix. There is no fixed length for first name and last name. It has only separator comma between two. I need to extract data from this column to populate into another table with 4 different columns such as First Name(25), Middle Name(25), Last Name(25) and suffix(3). Is there anyway, Can I extract using SQL into different columns? We are in z/OS DB2 V9.

    Eg.
    LUDICK, TIMOTHY
    BIDDLE, JOHN FRANKLIN
    KORMANIK, PAUL S
    WILFRED L POTTER

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    can we assume that the column is populated with both types of name structures?

    LUDICK, TIMOTHY with comma, so it's last name, first name, middle name, suffix
    and
    WILFRED L POTTER with no comma, so it's first name, middle name, last name, suffix
    ???
    or is WILFRED L POTTER a typo?

    and if the NAME contains a comma, will there always be a space after the comma?
    Last edited by dbzTHEdinosaur; 03-19-12 at 19:34. Reason: add additional questions
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    schintala, If the examples you supplied are valid, I don't think you can do what you want. The first 3 look like last-name ,/space first-name space nothing/middle-name/middle-initial.
    The last one looks like first-name middle-initial last-name

    While I can probably come up with something to break NAME out into 4 separate value:

    The first value could have a Last name or a First name
    The second value could have First name or Middle name/Middle initial
    The third value could have Nothing, Suffix, or Middle name/Middle Initial

    This would mix up the various parts of a name between the 4 columns (and not be very useful).

    Or are you relying on items such as Comma position to determine what format the name is. For example, these formats would work if:

    Code:
    Value1, Value2                :Is always Lastname, Firstname
    Value1, Value2, Value3        :Is always Lastname, Firstname, Suffix
    Value1, Value2 Value3         :Is always Lastname, Firstname, Middlename
    Value1, Value2 Value3, Value4 :Is always Lastname, Firstname, Middlename, Suffix
    Value1 Value2                 :Is always Firstname, Lastname
    Value1 Value2, Value3         :Is always Firstname, Lastname, Suffix
    Value1 Value2 Value3          :Is always Firstname, Middlename, Lastname
    Value1 Value2 Value3, Value4  :Is always Firstname, Middlename, Lastname, Suffix
    This would be more complex to process as all the various formats would need to be considered to correctly parse out the name parts.

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    TS decided on a COBOL solution to parse the column value and create the four new ones.

    his choice of solutions was posted on an mvs board where he originally posted this problem.

    i wish that cobol had the locate_in_string with the instance parm functionality.
    Dick Brenholtz, Ami in Deutschland

Posting Permissions

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