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

This would satisfy your sample data, but this would have to be adjusted to accommodate additional suffixes:

Code:

---

SQL> select name
  2    , regexp_substr(name, '[^ ]+') first
  3    , case when lower(replace(sf, '.')) in ('iii','jr','sr') and sp = 3
  4        then trim(regexp_substr(name, ' [^ ]+ '))
  5        when lower(replace(sf, '.')) not in ('iii','jr','sr') and sp = 2
  6        then trim(regexp_substr(name, ' [^ ]+ '))
  7      end middle
  8    , case when lower(replace(sf, '.')) not in ('iii','jr','sr') and sp is not null
  9        then regexp_substr(name, '[^ ]+$')
 10        when lower(replace(sf, '.')) in ('iii','jr','sr') and sp = 2
 11        then trim(regexp_substr(name, ' [^ ]+ '))
 12        when sp = 3
 13        then regexp_substr(name, '[^ ]+', 1, 3)
 14      end last
 15    , case when lower(replace(sf, '.')) in ('iii','jr','sr')
 16            then regexp_substr(name, '[^ ]+$')
 17      end suffix
 18  from (
 19    select replace(name, ',') name
 20            , regexp_substr(name, '[^ ]+$') sf
 21            , length(regexp_replace(name, '[^ ]')) sp
 22    from names)
 23  ;

NAME                FIRST          MIDDLE          LAST            SUFFIX
-------------------- --------------- --------------- --------------- ------
NIKIRA              NIKIRA
Nancy J. Schroeder  Nancy          J.              Schroeder
Hammontree Sr.      Hammontree                                      Sr.
Alan BROWN          Alan                            BROWN
Steve Sarvanidis III Steve                          Sarvanidis      III
W MCMILLIAN          W                              MCMILLIAN
Robert F. Seen Jr    Robert          F.              Seen            Jr

7 rows selected.

---

Nevertheless, PL/SQL would probably be a better alternative.