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
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:
, 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
I am sure I am missing the obvious...
DB2 v.7.1 OS/390
Thanks in advance