-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|