MVSFORUMS.com

[schintala]

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?

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

[dbzTHEdinosauer]

This topic has been discussed before (within last year) and solutions provided.
suggest you search the db2 forum.

or you can wait and somebody may provide it to you.
_________________
Dick Brenholtz
American living in Varel, Germany

[dbzTHEdinosauer]

My apologies, i searched and could not find it.

you have not given a sample column value that would generate the 4th column.
_________________
Dick Brenholtz
American living in Varel, Germany

[dbzTHEdinosauer]

i would use a case statement predicated on finding a comma,
WHEN POSSTR(NAME,',') > 0 then your order is last name, first name, middle initial/name, title
otherwise you know it is first, middle, last, title.

you can use a combination of POSSTR (or LOCATE) and SUBSTR
to extract the different portions of the column

to find the second and third space, you can use POSSTR(SUBSTR(NAME,POSSTR(NAME,' ') + 1))
as a starting point.

is it easy, no, but you wanted the sql.

i personnally would unload, then parse with sort and then use the sort output to feed a cobol program to update the new table.

or simply select NAME,
parse with cobol UNSTRING
then update the 4 columns with the host variables.
_________________
Dick Brenholtz
American living in Varel, Germany

[kolusu]

schintala,

Please post with a meaningful titles. A simple "sql help" isn't going to help. I edit the title. Also it would have been nice if you have posted the desired output from the above sample.

I haven't done the suffix part( or lack of data as you show), but here is a untested version for splitting the 25 byte column into 3 separate fields

[schintala]

Kolusu,
Thank you so much for your valuable update on the SQL. We have developed cobol program to read the data from the name column and inserted into other columns after splitting. As the data in name column is not consistent, we got to update data through program. Thank you for your suggestion and make sure I'll have meaningful title in future postings.

[dbzTHEdinosauer]

schintala,
thx for the follow-up.
_________________
Dick Brenholtz
American living in Varel, Germany