Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extract FirstName and LastName from FullName

verde1030Feb 1 2021

Hello all, could use some help. I am having trouble extracting the first and last names from fullname field and unfortunately every name is not in exactly the same format.
I have 2 tables as below , and my goal is to extract firstname and lastname from both of these tables separately and join them later using these firstname and lastname as Keys (there are no corresponsing ID fields for these names, so my only way is to join on these character fields). Also data is around 10k rows in each table, so i am guessing performance shouldn't be an issue.
I was able to notice a pattern for table_1 and table_2 as noted in comments below.
For simplicity purposes i am only using 1 name as a sample . (FirstName = DAVIS, LastName = ROBERT)
My oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

with
table_1 ( uw_id, uw_name ) as (
-- If there is a comma the format is LastName, FirstName
--If there is no comma the format is FirstName LastName (or) FirstName MI LastName
select '0001', 'ROBERT, DAVIS' from dual union all
select '0001', 'DAVIS C M ROBERT' from dual union all
select '0001', 'DAVIS ROBERT' from dual
)
,
table_2 ( uw_id, uw_name ) as (
--Last Name always appears in the beginning but different variations like below
select '0001', 'ROBERT,DAVIS C M.' from dual union all
select '0001', 'ROBERT,DAVIS C M' from dual union all
select '0001', 'ROBERT,DAVIS CARLOS MEND' from dual union all
select '0001', 'ROBERT,DAVIS' from dual
)
--This is my code that i started to write but got stuck
select uw_id, uw_name,
substr(uw_name, 1, instr(uw_name, ' ') - 1) as first_name,
substr(uw_name, instr(uw_name, ' ') + 1) as last_name
from table_1
union all
select uw_id, uw_name,
substr(uw_name, 1, instr(uw_name, ' ') - 1) as first_name,
substr(uw_name, instr(uw_name, ' ') + 1) as last_name
from table_2

Expected Result Set:
select '0001' uw_id , 'ROBERT, DAVIS' uw_name,'DAVIS' first_name, 'ROBERT' last_name from dual union all
select '0001', 'DAVIS C M ROBERT','DAVIS' first_name, 'ROBERT' last_name from dual union all
select '0001', 'DAVIS ROBERT','DAVIS' first_name, 'ROBERT' last_name from dual union all
select '0001', 'ROBERT,DAVIS C M.','DAVIS' first_name, 'ROBERT' last_name from dual union all
select '0001', 'ROBERT,DAVIS C M','DAVIS' first_name, 'ROBERT' last_name from dual union all
select '0001', 'ROBERT,DAVIS CARLOS MEND','DAVIS' first_name, 'ROBERT' last_name from dual union all
select '0001', 'ROBERT,DAVIS','DAVIS' first_name, 'ROBERT' last_name from dual

Any thoughts are greatly appreciated.
Thank you!

This post has been answered by Frank Kulash on Feb 11 2021
Jump to Answer
Comments
Post Details
Added on Feb 1 2021
9 comments
14,470 views