all name in one column need separated
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: all name in one column need separated

  1. #1
    Join Date
    Nov 2000
    Posts
    172

    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
    Last edited by kburrows; 02-11-2009 at 02:24 PM.

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width