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!