DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: all name in one column need separated

Threaded View

  1. #1
    Join Date
    Nov 2000
    Posts
    175

    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 03:24 PM.

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