如何在CLR函数中获取SQL字符串的排序规则？ [英] How can I get a SQL String's collation within a CLR function?

问题描述

我正在用C＃编写一个Levenshtein距离函数，以计算两个字符串之间的编辑距离。问题是我想用不同的排序规则多次调用该方法，但是只有一种排序规则才能跨SQL到CLR接口进行它-这是数据库的默认排序规则。

I'm writing a Levenshtein Distance function in C# to calculate the edit distance between two strings. The problem is that I'd like to call the method multiple times with different collations but only one collation ever makes it across the SQL to CLR interface - and that is the default collation of the database.

这是CLR函数的代码：

Here is the code for the CLR Function:

[SqlFunction(IsDeterministic = true, Name = "LevenshteinDistance")]
public static SqlInt64 Distance(SqlString textA, SqlString textB)
{
    // get a collation-aware comparer so string/character comparisons 
    // will match the inputs' specified collation
    var aCompareInfo = textA.CompareInfo;
    var compareOptions = ConvertCompareOptions(textA.SqlCompareOptions);
    var aLength = textA.Value.Length;
    var bLength = textB.Value.Length;

    // degenerate cases
    if (aCompareInfo.Compare(textA.Value, 0, aLength, textB.Value, 0, bLength, compareOptions) == 0) { return 0; }
    if (aLength == 0) { return bLength; }
    if (bLength == 0) { return aLength; }

    // create two work vectors of integer distances
    var previousDistances = new SqlInt64[Maximum(aLength, bLength) + 1];
    var currentDistances = new SqlInt64[Maximum(aLength, bLength) + 1];

    // initialize previousDistances (the previous row of distances)
    // this row is A[0][i]: edit distance for an empty textA
    // the distance is just the number of characters to delete from textB
    for (var i = 0; i < previousDistances.Length; i++)
    {
        previousDistances[i] = i;
    }

    for (var i = 0; i < aLength; i++)
    {
        // calculate currentDistances from the previous row previousDistances

        // first element of currentDistances is A[i+1][0]
        //   edit distance is delete (i+1) chars from textA to match empty textB
        currentDistances[0] = i + 1;

        // use formula to fill in the rest of the row
        for (var j = 0; j < bLength; j++)
        {
            var cost = (aCompareInfo.Compare(textA.Value, i, 1, textB.Value, j, 1, compareOptions) == 0) ? 0 : 1;
            currentDistances[j + 1] = Minimum(currentDistances[j] + 1, previousDistances[j + 1] + 1, previousDistances[j] + cost);
        }

        // copy currentDistances to previousDistances for next iteration
        for (var j = 0; j < previousDistances.Length; j++)
        {
            previousDistances[j] = currentDistances[j];
        }
    }

    return currentDistances[bLength];
}

在将CLR程序集部署到SQL Server（2008 R2）后像这样调用它

After deploying the CLR assembly to SQL Server (2008 R2) and calling it like this:

print dbo.LevenshteinDistance('abc' collate Latin1_General_CI_AI, 'ABC' collate Latin1_General_CI_AI)
print dbo.LevenshteinDistance('abc' collate Latin1_General_CS_AS_KS_WS, N'ABC' collate Latin1_General_CS_AS_KS_WS)

两个调用都返回零（0）。因为我为第二个调用指定了区分大小写的排序规则，所以我希望第二个调用返回三（3）。

Both calls return zero (0). Because I specified a case-sensitive collation for the second call, I expected that second call to return three (3).

在SQL Server中使用CLR函数，是否可能指定数据库默认以外的排序规则，并在CLR函数中使用它们？如果是，怎么办？

Using CLR functions in SQL Server, is it possible to specify collations other than the database default and have them used within a CLR function? If so, how?

推荐答案

在互联网上看不到任何替代方法或对此问题的回答，我决定指定所需的排序规则属性作为函数参数，并根据输入或从数据库传入的默认排序规则选择 CultureInfo 对象和 CompareOptions 。

Not seeing any alternatives on the Internet or responses to this question, I decided to specify the desired collation attributes as function parameters and select a CultureInfo object and CompareOptions based upon the inputs or the default collation passed in from the database.

[SqlFunction(IsDeterministic = true, Name = "LevenshteinDistance")]
public static SqlInt64 Distance(SqlString textA, SqlString textB, int? lcid, bool? caseInsensitive, bool? accentInsensitive, bool? kanaInsensitive, bool? widthInsensitive)
{
    // get a collation-aware comparer so string/character comparisons 
    // will match the inputs' specified collation
    //var aCompareInfo = textA.CompareInfo;
    var aCompareInfo = CultureInfo.GetCultureInfo(lcid ?? textA.LCID).CompareInfo;
    //var compareOptions = ConvertCompareOptions(textA.SqlCompareOptions);
    var compareOptions = GetCompareOptions(caseInsensitive, accentInsensitive, kanaInsensitive, widthInsensitive);

    // ...  more code ...

    // first comparison
    if (aCompareInfo.Compare(textA.Value, 0, aLength, textB.Value, 0, bLength, compareOptions) == 0) { return 0; }

    // ...  more code ...

    var cost = (aCompareInfo.Compare(textA.Value, i, 1, textB.Value, j, 1, compareOptions) == 0) ? 0 : 1;

    // ...  more code ...
}

private static CompareOptions GetCompareOptions(bool? caseInsensitive, bool? accentInsensitive, bool? kanaInsensitive, bool? widthInsensitive)
{
    var compareOptions = CompareOptions.None;

    compareOptions |= (caseInsensitive ?? false) ? CompareOptions.IgnoreCase : CompareOptions.None;
    compareOptions |= (accentInsensitive ?? false) ? CompareOptions.IgnoreNonSpace : CompareOptions.None;
    compareOptions |= (kanaInsensitive ?? false) ? CompareOptions.IgnoreKanaType : CompareOptions.None;
    compareOptions |= (widthInsensitive ?? false) ? CompareOptions.IgnoreWidth : CompareOptions.None;

    return compareOptions;
}

更新程序集和UDF声明后，可以这样调用函数：

After updating my assembly and UDF declarations, I can call the function like so:

print dbo.LevenshteinDistance('abc', 'ABC', null, 1, 1, 1, 1)
print dbo.LevenshteinDistance('abc', 'ABC', null, 0, 0, 0, 0)

现在，第一个调用返回0（数据库默认区域性，所有内容不敏感），而第二个调用返回3（数据库默认区域性，所有内容敏感）。

And now the first call returns 0 (database default culture, everything Insensitive) while the second call returns 3 (database default culture, everything Sensitive).

这篇关于如何在CLR函数中获取SQL字符串的排序规则？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！