spliting a name field into multiple fields

[edibrian]

I need to split a name field into multiple fields
The FROM field format is ACCTNAME VARCHAR2(60) and the data is stored as "DOE, JOHN A" or "DOE JR, JOHN A"

The TO fields formats are:
LNAME VARCHAR2(35)
FNAME VARCHAR2(25)
MNAME VARCHAR2(1)

How would I split the ACCTNAME into the LNAME, FNAME, MNAME fields using SQL/SQLPLUS on Oracle 9i?

I need everything up to the comma in field LNAME, everything after the comma to the last space before the end of the actual data in field FNAME and the last character of the actual data in field MNAME

[jkoopmann]

just so long as your data is in the format you say. there will be a problem if there is no middle initial. in that case you will have to wrap some more logic around this.

since the sql is hard to read and i can't seem to get the formating in the reply quite wright, i have attatched a text file for better viewing.

select acctname,
substr(ACCTNAME,1,instr(ACCTNAME,',')-1)LNAME,
ltrim(substr(ACCTNAME,instr(ACCTNAME,',')+1,instr( ACCTNAME,' ',-1)-instr(ACCTNAME,','))) FNAME,
substr(ACCTNAME,instr(ACCTNAME,' ',-1)+1) MNAME
from name_table