all name in one column need separated

[kburrows]

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

[ebrian]

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

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.