SQL Server模糊搜索（匹配百分比）

这就是我可以实现的方式：

进一步解释@ SQL Server模糊搜索-Levenshtein算法

使用您选择的任何编辑器创建以下文件：

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class StoredFunctions{    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = false)]    public static SqlDouble Levenshtein(SqlString stringOne, SqlString stringTwo)    {        #region Handle for Null value        if (stringOne.IsNull) stringOne = new SqlString("");        if (stringTwo.IsNull) stringTwo = new SqlString("");        #endregion        #region Convert to Uppercase        string stroneUppercase = stringOne.Value.ToUpper();        string strTwoUppercase = stringTwo.Value.ToUpper();        #endregion        #region Quick Check and quick match score        int stroneLength = strOneUppercase.Length;        int strTwoLength = strTwoUppercase.Length;        int[,] dimention = new int[stroneLength + 1, strTwoLength + 1];        int matchCost = 0;        if (stroneLength + strTwoLength == 0)        { return 100;        }        else if (stroneLength == 0)        { return 0;        }        else if (strTwoLength == 0)        { return 0;        }        #endregion        #region Levenshtein Formula        for (int i = 0; i <= strOneLength; i++) dimention[i, 0] = i;        for (int j = 0; j <= strTwoLength; j++) dimention[0, j] = j;        for (int i = 1; i <= strOneLength; i++)        { for (int j = 1; j <= strTwoLength; j++) {     if (strOneUppercase[i - 1] == strTwoUppercase[j - 1])         matchCost = 0;     else         matchCost = 1;     dimention[i, j] = System.Math.Min(System.Math.Min(dimention[i - 1, j] + 1, dimention[i, j - 1] + 1), dimention[i - 1, j - 1] + matchCost); }        }        #endregion        // Calculate Percentage of match        double percentage = System.Math.Round((1.0 - ((double)dimention[strOneLength, strTwoLength] / (double)System.Math.Max(strOneLength, strTwoLength))) * 100.0, 2);        return percentage;    }};

命名为 levenshtein.cs

转到命令提示符。转到levenshtein.cs的文件目录，然后调用 csc.exe / t：库/ out：UserFunctions.dll
levenshtein.cs，
您可能必须提供 NETframeWork 2.0中csc.exe的完整路径。

DLL准备好后。将其添加到程序集 数据库 >>可编程性>>程序集 > >新建程序集。

在数据库中创建函数：

CREATE FUNCTION dbo.LevenshteinSVF    (      @S1 NVARCHAr(200) ,      @S2 NVARCHAr(200)    )RETURNS FLOATAS EXTERNAL NAME    UserFunctions.StoredFunctions.LevenshteinGO

就我而言，我必须启用clr：

sp_configure 'clr enabled', 1GOreconfigureGO

测试功能：

SELECT  dbo.LevenshteinSVF('James','James Bond')

结果：50％匹配