DIFFERENCE和SOUNDEX SQL函数的概述及实例

吴欣
561 阅读5分钟

这篇文章概述了DIFFERENCE和SOUNDEX SQL Server的内置系统函数。它解释了如何使用这些函数以及它们是如何工作的。

简介

Soundex是由Robert C. Russell和Margaret King Odell在20世纪初开发的一种语音算法。这种算法是用来索引名字在英语中的发音的。这种算法的主要目标是将同音字编码为相同的表述，以便进行匹配，即使有一些轻微的拼写差异。作为一个例子，考虑 "Smith "和 "Smyth"，或者 "Mohamad "和 "Mouhammad "这两个名字。Soundex主要是对辅音进行编码，只有当元音是名字的第一个字母时才会进行编码。

作为最流行的语音算法之一，Soundex已经在多个数据库引擎中实现，如Oracle、SQL Server、MySQL、SQLite和PostgreSQL。

在下面的章节中，我们将说明Soundex SQL Server的功能。

SOUNDEX SQL Server内置函数

在SQL Server中，SOUNDEX()是一个标量函数，它接受一个字符串值作为输入，并返回一个四字符的字符串。如前所述，Soundex SQL函数返回的字符串是根据输入字符串的英语口语方式生成的。

  • 代码的第一个字符是输入字符串的第一个字符，转换为大写。
  • 代码的其余字符是数字，代表表达式中的字母。
  • 元音以及 "H "和 "W "字符被忽略，除非它们是输入字符串的第一个字母。
  • 如果输入字符串的长度小于4，Soundex函数会在返回值中增加额外的零。

Soundex SQL函数主要作为一种模糊匹配技术用于数据整合。它是一个对排序敏感的函数。

正如官方文档中提到的，在SQL Server 2012之前，Soundex SQL函数只应用Soundex算法规则的一个子集。在SQL Server 2012中，更多的规则被实施。此外，一旦使用大于或等于110（SQL Server 2012）的数据库兼容性级别，将应用以下限制。

  • 包含用SOUNDEX定义的持久化计算列的堆（没有聚类索引的表）不能被查询，直到通过运行ALTER TABLE REBUILD语句重建堆。
  • 用SOUNDEX定义的CHECK约束在升级时被禁用。要启用该约束，我们应该运行语句ALTER TABLE
    • WITH CHECK CHECK CONSTRAINT ALL。
  • 包含用SOUNDEX定义的持久化计算列的索引（包括索引视图）不能被查询，直到通过运行语句ALTER INDEX ALL ON REBUILD重建该索引。

    例子

    为了说明Soundex SQL函数是如何工作的，我们将运行一些例子。首先，让我们运行下面的查询。

    • SELECT SOUNDEXSELECT SOUNDEX('H') as 'H', SOUNDEX('Ha') as ' Ha',

    SOUNDEX('Hd') as 'Hd', SOUNDEX('Had') as ' Had', SOUNDEX('Hadi') as ' Hadi'

    下面的图片显示，Soundex函数对 "H "和 "Ha "这两个输入字符串返回相同的值（H000）。我们可以看到，"a "被忽略了，而三个额外的零被添加到结果中。

    此外，"Hd"，"Had "和 "Hadi "产生相同的输出（H300），因为 "a "和 "I "元音被忽略了，而且还增加了两个零。

    Soundex SQL function example

    图1 - Soundex函数示例

    让我们尝试将多个单词传递给Soundex函数。

    SELECT SOUNDEX('Hadi') as 'Hadi',

    SOUNDEX('HadiFadlallah') as 'HadiFadlallah',

    SOUNDEX('Hadi Fadlallah') as 'Hadi Fadlallah' 。

    下面的图片显示，"Hadi "和 "Hadi Fadlallah "产生了相同的输出值（H300），空格后的字符被忽略了。而去掉名字中的空格，输出值将变为 "H313"。此外，"HadiFadlallah "的输出值与前四个辅音（"Hdf"）的输出值相同。

    Another example of the Soundex SQL function

    图2 - Soundex函数的另一个例子

    DIFFERENCE是一个内置的标量函数，用于使用Soundex SQL函数测量两个字符串的相似度。首先，SOUNDEX()被应用于每个输入，然后对这些结果进行相似度检查。这个函数返回一个在0到4之间的整数值。当这个值接近于4时，说明输入的内容非常相似。

    例子

    为了说明Difference()SQL函数如何工作，我们将运行一些例子。首先，让我们运行以下查询。

    SELECT DIFFERENCE('H','Ha') as 'H_Ha',

    DIFFERENCE('Ha','Had') 作为 'Ha_Had'。

    DIFFERENCE('Hd','Had') as 'Hd_Had',

    DIFFERENCE('Had','Hadi') as 'Had_Hadi'

    下面的图片显示，具有相同Soundex输出的数值，如 "H "和 "Ha "或 "Hd"、"Had "和 "Hadi "的相似度为4，而 "Ha "和 "Had "产生的相似度较低。

    Difference SQL function example

    图3--差异SQL函数示例

    此外，下面的例子显示，添加空格会使Difference()函数忽略右边的所有字符。

    SELECT DIFFERENCE('Hadi','Hadi Fadlallah') as 'Hadi_Hadi Fadlallah',

    DIFFERENCE('Hadi','HadiFadlallah') as 'Hadi_HadiFadlallah'

    我们可以注意到，"Hadi "和 "Hadi Fadlallah "的相似度为4分，而去掉 "Hadi Fadlallah "的空格后，得分将降至2分。

    Second Difference SQL function example

    图4--Difference SQL函数的第二个例子

    现在，让我们尝试将 "MSSQL "中的两个词与 "MYSQL "和另一个词 "POSTGRES "这样的相似词进行比较。

    SELECT DIFFERENCE('MYSQL', 'MSSQL') as 'MYSQL_MSSQL',

    DIFFERENCE('MSSQL', 'POSTGRES') as 'MSSQL_POSTGRES'

    下面的图片显示了 "MYSQL "和 "MSSQL "的相似度得分很高，因为这两个词之间只有一个字符不同，而 "MSSQL "和 "POSTGRES "的得分很低，因为它们有很大的不同。

    Third example of the Difference SQL function

    图5 - 差异SQL函数的第三个例子

    总结

    这篇文章简要地解释了Soundex算法以及它是如何在SQL Server中实现的。此外，它还说明了另一个名为Difference的内置函数，它使用Soundex SQL函数来计算两个不同输入字符串之间的相似性。

avatar
文章
阅读
粉丝