all name in one column need separated
All,
I have one columns that contains the firstname, MI, lastname, suffix.
I am trying to separate the firstname MI lastname suffix from one column into 4 separate columns (fn, mi, ln, suffix).
I have this but, it doesn't work on all of them.
Select item_contact_id,
ITEM_CONTACT_LAST_NAME,
SUBSTR(ITEM_CONTACT_LAST_NAME,1,DECODE(INSTR(ITEM_CONTACT_LAST_NAME, ' '), 0,99,INSTR(ITEM_CONTACT_LAST_NAME, ' '))) as first_name ,
SUBSTR(ITEM_CONTACT_LAST_NAME,LENGTH(RTRIM(ITEM_CONTACT_LAST_NAME, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))+1) as last_name
From ITEM_CONTACT
where ITEM_CONTACT_LAST_NAME like '% %'
order by 1, 2, 3
Sample DATA from the last_name column.
NIKIRA
Nancy J. Schroeder
Hammontree, Sr.
Alan BROWN
Steve Sarvanidis III
W MCMILLIAN
Robert F. Seen Jr
Thanks,
Kathy