如何通过部分匹配来联接两个表 [英] How to join two table with partial match

问题描述

我有两个包含以下数据的表:

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

我尝试使用下面的join语句，但是它对于带有的任何行似乎都不起作用.在里面.

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.

我可以尝试使用RIGHT或LEFT函数对数据进行规范化，但是对于没有'.'的行.它会抛出一个错误.而且我不知道如何跳过没有."的行.

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?

推荐答案

在您的示例中，某些情况下标题较长，而在其他情况下，名称较长(如果您想加入标题中包含名称的任何值)或标题是您可以使用的名称:

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.

作为最有效的方法，您希望在表中有一个标准化字段，可以通过等式(例如a.col1 = b.col1)用于JOIN，因此这就需要剥夺每个领域的核心，使其成为值得加入的领域.

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.

更新:如果重要的部分是第一个句点之前的所有内容，那么您要使用LEFT()和CHARINDEX()的组合(以及CASE语句，因为并非所有字符串都包含句点):

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  

您也可以在JOIN中使用以上内容:

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

这篇关于如何通过部分匹配来联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！