T-SQL:比较 2 列与未知值 [英] T-SQL: compare 2 columns with unknown values

问题描述

我正在加入一张桌子，以便在我的桌子上找到同一个家庭中姓氏不同的人.唯一的问题是在某些情况下，一个姓氏可能是 Jones，而对于另一条记录，该列可能是 Jones Jr.

I'm joining a table to itself in order to find people in my table in the same family with different last names. The only issue is there are instances where one last name might be Jones, and for another record the column might be Jones Jr.

这些在技术上是相同的姓氏，因此它们不符合我的要求.我需要从我的结果中消除小琼斯.

These are technically the same last name so they don't fit my requirements. I need to eliminate Jones Jr. from my results.

复杂的因素是它也可能是 Smith-Jones 之类的东西，所以我也需要删除此记录.由于我不知道区别在哪里，我希望能够在我的查询中添加一个条件，说明每个名称的匹配字符不超过 4 个(或任意数量).

The complicating factor is it could also be something like Smith-Jones, so I'd need to remove this record too. Since I don't know where the difference will be I would like to be able to add a condition to my query saying that no more than 4 (or some arbitrary number) characters of each name can match.

这是我的查询:

SELECT [fields] 
FROM [table] a 
INNER JOIN [table] b ON a.[family_id] = b.[family_id]
WHERE a.[last_name] <> b.[last_name]

有什么想法吗?

推荐答案

您可以使用 DIFFERENCE.返回的值是 SOUNDEX 值中相同的字符数.返回值的范围为 0 到 4:0 表示相似度弱或不相似，4 表示相似度强或值相同.这将是您要求的一个很好的解决方案:在我的桌子上找到同一个家庭中姓氏不同的人

You could use DIFFERENCE. The value returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values. This would be a nice solution to your requirement: find people in my table in the same family with different last names

SELECT [fields] from [table] a 
INNER JOIN [table] b
ON a.[family_id] = b.[family_id]
WHERE DIFFERENCE(a.[last_name], b.[last_name]) < 4

这篇关于T-SQL:比较 2 列与未知值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！