CODEWITHSUNDEEP
sqlteradatateradata-sql-assistantteradatasql
User56756
User56756

Reputation: 352

Match names from one table with names from other

I have a table

CID FirstName LastName Loc
123 Brad Shaw ASD
234 Chad Reed SES
345 Bryan Miles WED
456 Mr Bradley Knight ASD
567 Doctor Chadwick Prince WED
678 Mrs Bryana Yang SES
789 Arty-stry Blue POR

Another table

FirstName
Bryan
Ben
Brad
Chad

I wish to have a result matching all the names from 2nd table with names in 1st table, no matter if names in 2nd table form sort of substrings of 1st table.

CID FirstName LastName Loc TABLE2_MATCH
123 Brad Shaw ASD Brad
234 Chad Reed SES Chad
345 Bryan Miles WED Bryan
456 Mr Bradley Knight ASD Brad
567 Doctor Chadwick Prince WED Chad
678 Mrs Bryana Yang SES Bryan
789 Arty-stry Blue POR No_match

I have thousands of names and I need to find a better way of doing this, currently I am trying to do IN statement but that matches exact names, where as I want substrings to match as well. I hope this is clear. Happy to give more info

Upvotes: 0

Views: 1435

Answers (1)

Bohemian
Bohemian

Reputation: 425073

Find all outer joins where FirstName from table1 is like table2's FirstName wrapped in '%' :

select
  a.CID,
  a.FirstName,
  a.LastName,
  a.Loc,
  coalesce(b.FirstName, 'No_match') as TABLE2_MATCH
from table1 a
left join table2 b on lower(a.FirstName) like '%' || lower(b.FirstName) || '%'

String X being a substring of string Y can be expressed as Y like '%X%'.
Calls to lower() make it case insensitive, although see if it still works if you remove calls to lower().

Upvotes: 1

Related Questions