Unstringing names

[notadba]

Hi,

Have been messing around with this for a while and thought I would go to the experts rather than banging my head against the wall any further...

I have a name field that stores name in the format of
Surname,Firstname 2ndname 3rdname etc in a char(70) field

eg
Smith,John Edward

I have the code to split out the surname, all given names and have worked out from second given name onwards - but can't get the first name to split out on its own.

The basic code I have is:

SELECT A.NAME
, SUBSTR(A.NAME,1,(POSSTR(A.NAME,','))-1) AS SURNAME
, SUBSTR(A.NAME,(POSSTR(A.NAME,',')+1),70) AS ALL_GIVEN_NAMES
, SUBSTR(SUBSTR(A.NAME,(POSSTR(A.NAME,',')+1),70),(POSSTR(SUBSTR(A.NAME,(POSSTR(A.NAME,(,',')+1),70),' ')+1),70) AS 2ND_3RD_NAME
FROM NAME_TABLE A
FETCH FIRST 100 ROWS ONLY WITH UR

Every name field will have a comma after the surname, and I need to find the first space after the comma to find the end of the first name (this gets around people with spaces in the surname.

I am sure I am missing the obvious...
DB2 v.7.1 OS/390

Thanks in advance

 

[MarcLodge]

Probably best if you post some of the data as without it we will struggle to fully understand the problem.

My initial thoughts were that you should change the lookup for a comma to a comma and a space, but I assume that it's not that easy. Hence my request for a better idea of the data.

Marc