How to join two table with partial match

Question

I have two table with the following data:

TableA.name
R4.23-Core-2
R4.23-Core-2
LA#213 CGHPBXsw01 127.213 0024-737e-e341
LA#252 CGHRack1sw01 127.252 0022-57ab-d781
SOC-01A-SW01
to - R4-DISTR-9512
to-R2-DISTR-5900-1
to-R3.25-EDGE

TableB.caption
R4.23-Core-2.ehd.ca
R4.23-Core-2.nhd.ca
CGHPBXsw01
CGHRack1sw01
SOC-01A-SW01
R4-DISTR-9512
R2-DISTR-5900-1.phsnc.
R3.25-EDGE.phsne.edjc.ca

I've tried using the following join statement but it doesn't seem to work for any row with a . in it.

dbo.TableA.Name 
INNER JOIN dbo.TableB.Caption 
  ON dbo.TableA.Name LIKE '%' + dbo.TableB.Caption + '%'

I also try using replace function, which work but there are too much variant to include with replace.

I could try using the RIGHT or LEFT function to normalize the data but for row that doesn't have '.' it would throw an error. And I don't know how to skip row that doesn't have '.'

What is the most efficient way to join these two table?

Answer 1

In some situations in your example the caption is longer, and other times the name is longer, if you wanted to join on any value where name is in the caption or caption is in the name you could use:

dbo.TableA.Name 
INNER JOIN dbo.TableB.Caption 
   ON   dbo.TableA.Name LIKE '%' + dbo.TableB.Caption + '%'
     OR dbo.TableB.Caption LIKE '%' + dbo.TableA.Name + '%'

That could explain why your query isn't working as expected.

As far as the most efficient way to do this, you'd want to have a standardized field in your table that you could use to JOIN on via equality (ex. a.col1 = b.col1), so that would entail stripping out the heart of each field that makes it join-worthy.

Update: If the important part is everything before the first period, then you want to use a combination of LEFT() and CHARINDEX() (and a CASE statement since not all strings contain a period):

SELECT NewField = CASE WHEN CHARINDEX('.',Name) > 0 THEN  LEFT(Name,CHARINDEX('.',Name)-1) 
                       ELSE Name
                  END
FROM YourTable  

You could use the above in your JOIN too:

dbo.TableA.Name 
INNER JOIN dbo.TableB.Caption 
  ON CASE WHEN CHARINDEX('.',TableA.Name) > 0 THEN  LEFT(TableA.Name,CHARINDEX('.',TableA.Name)-1) 
                           ELSE TableA.Name
                      END
    = CASE WHEN CHARINDEX('.',TableB.Caption) > 0 THEN  LEFT(TableB.Caption,CHARINDEX('.',TableB.Caption)-1) 
                           ELSE TableB.Caption
                      END

Answer 2

How about this ( Not Tested)

dbo.TableA
INNER JOIN dbo.TableB 
      ON CHARINDEX(dbo.TableB.Caption, dbo.TableA.Name) > 0

Test it and don't forget Upvote OR accept.