CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
c_c
c_c

Reputation: 35

Match multiple first names

I would like to find a RegEx that allows matching (SQL join) without taking into consideration the order or full string... as following:

Case 1
Left: Mr. John Doe
Right: Doe John
Result: match

Case 2
Left: John Doe
Right Doe
Result: match

Case 3
Left: Robert-John Doe
Right: Robert Doe
Result: match

etc...

Is this possible? I will add this in the join condition of two tables in Oracle SQL.

Upvotes: 2

Views: 65

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

You can use regexp_substr and regexp_count together as :

with t( id, col1, col2 ) as
(
 select 1, 'Mr. John Doe'   , 'Doe John'   from dual union all
 select 2, 'John Doe'       , 'Doe'        from dual union all
 select 3, 'Robert-John Doe', 'Robert Do'  from dual        
), t2 as
(
select distinct t.*, 
       regexp_substr(col1, '[^ ]+', 1, level) as col01, 
       regexp_substr(col2, '[^ ]+', 1, level) as col02,
       level                
  from dual 
 cross join t
connect by level <= greatest(regexp_count(col1, '[^ ]+'),regexp_count(col2, '[^ ]+'))  
 order by id, level
 )
select distinct id, col1, col2, 'matched' as status
  from t2 t
 where exists ( select 1 from t2 where id = t.id and ( col01 = t.col02 or col02 = t.col01 ) )
 union all
select distinct id, col1, col2, 'Not matched'
  from t2 t
 where not exists ( select 1 from t2 where id = t.id and ( col01 = t.col02 or col02 = t.col01 ) )
 order by id;

ID  COL1            COL2        STATUS
--  --------------- ----------  -----------
1   Mr. John Doe    Doe John    matched
2   John Doe        Doe         matched
3   Robert-John Doe Robert Do   Not matched

Demo

P.S. I changed row three slightly.

Upvotes: 3

Related Questions