This is a great extension to have for mssql. There is more information about what the function does on the levenshtein wiki page http://en.wikipedia.org/wiki/Levenshtein_distance in short it will give you a number of changes (add / delete / change) to get from the first string to the second string.

For example: "Hello World" and "Hello Worlds" has a distance of 1. This can be great if you are trying to do searches where you want to pick up on typo's or spelling mistakes.

The c# code for the extension is

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = false)]
public static SqlInt32 Levenshtein(SqlString S1, SqlString S2)
{
    if (S1.IsNull || S2.IsNull)
        throw (new ArgumentNullException());

    int n = S1.Value.Length;
    int m = S2.Value.Length;

    int[,] d = new int[n + 1, m + 1];
    int cost = 0;

    if (n == 0)
        return m;
    if (m == 0)
        return n;

    for (int i = 0; i <= n; i++)
        d[i, 0] = i;

    for (int j = 0; j <= m; j++)
        d[0, j] = j;

    for (int i = 1; i <= n; i++)
    {
        for (int j = 1; j <= m; j++)
        {
            if (S1.Value[i-1] == S2.Value[j-1])
                cost = 0;
            else
                cost = 1;
            d[i, j] = System.Math.Min(System.Math.Min(d[i - 1, j] + 1, d[i, j - 1] + 1), d[i - 1, j - 1] + cost);
        }
    }

    return d[n, m];
}

Once compiled you can create a function for mssql with the following code.

CREATE Function Levenshtein(@S1 nvarchar(512), @S2 nvarchar(512))
        RETURNS int as EXTERNAL NAME SqlExtensions.UserDefinedFunctions.Levenshtein
GO

You can read more about enabling clr support in mssql here http://www.stev.org/post/2011/01/30/Enabling-MSSQL-CLR-Functions.aspx





Last Modified: 04 April 2017

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-10-27 - CSharp - Number Of Cores and Processors
2011-10-04 - CSharp - Thread Queue Example
2011-09-29 - CSharp - Background Thread / Task
2011-09-24 - MSSQL - Checking Uptime
2011-09-17 - CSharp - Lookup Hostname
2011-08-29 - CSharp - IsGuid
2011-08-29 - C Sharp - StopWatch, The high resolution timer
2011-08-26 - CSharp - Convert Between Meters and Feet
2011-08-23 - CSharp - ThreadPool and Windows Forms
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-10 - C Sharp - MD5Sum / SHASum
2011-08-09 - MSSQL - Last Backup time and size
2011-07-22 - CSharp - Fibonacci sum
2011-07-13 - CSharp - Palindrome
2011-07-11 - CSharp - The string Reverse
2011-07-10 - CSharp - Interviews FizzBuzz
2011-06-27 - CSharp - HowTo Parse a URL
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-22 - CSharp - Extending any class
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-13 - ASPNET - Login by username or email
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-12 - CSharp - ASPNET Logoff
2011-05-05 - MSSQL - Log Sizes
2011-03-07 - C Sharp - Windows Logoff
2011-03-02 - ASPNET - Dynamic Controls
2011-03-01 - C Sharp - Resize an image by size of height
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-10 - CSharp - Shutdown or reboot windows
2011-02-10 - CSharp - Wake on Lan (WOL) Packet
2011-02-06 - C Sharp - Gridview Bound Yes No
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-29 - MSSQL - TRIM Function