Check If Similar Value Exists In Database

I want to check if an equal or a similar value exists in database. I have build this code: SqlConnection con1 = new SqlConnection(); con1.ConnectionString = ConfigurationManager.Co

Solution 1:

You could use a Levenshtein distance algorithm in T-SQL. For example (from here):

CREATEFUNCTION dbo.Levenshtein(@s nvarchar(4000), @t nvarchar(4000), @dint)
RETURNSintASBEGINDECLARE@slint, @tlint, @iint, @jint, @scnchar, @cint, @c1int,
    @cv0 nvarchar(4000), @cv1 nvarchar(4000), @cminintSELECT@sl= LEN(@s), @tl= LEN(@t), @cv1='', @j=1, @i=1, @c=0
  WHILE @j<=@tlSELECT@cv1=@cv1+NCHAR(@j), @j=@j+1
  WHILE @i<=@slBEGINSELECT@sc=SUBSTRING(@s, @i, 1), @c1=@i, @c=@i, @cv0='', @j=1, @cmin=4000
    WHILE @j<=@tlBEGINSET@c=@c+1SET@c1=@c1-CASEWHEN@sc=SUBSTRING(@t, @j, 1) THEN1ELSE0END
      IF @c>@c1SET@c=@c1SET@c1= UNICODE(SUBSTRING(@cv1, @j, 1)) +1
      IF @c>@c1SET@c=@c1
      IF @c<@cminSET@cmin=@cSELECT@cv0=@cv0+NCHAR(@c), @j=@j+1END
    IF @cmin>@d BREAK
    SELECT@cv1=@cv0, @i=@i+1ENDRETURNCASEWHEN@cmin<=@dAND@c<=@dTHEN@cELSE-1ENDEND
GO

Now something like this works:

SELECT Kod, Levenshtein  = dbo.Levenshtein(Kod, @name, 2)
FROM SMS_KOD
WHERE dbo.Levenshtein(Kod, @name, 2) <>-1

You can use a SqlDataAdapter to fill a DataTable. If it contains rows there are at least similar Kods.

Sql-Fiddle

Here is a possible implementation:

string sql = @"SELECT Kod, Levenshtein = dbo.Levenshtein(Kod, @name, 2)
               FROM SMS_KOD
               WHERE dbo.Levenshtein(Kod, @name, 2) <> -1";

var table = new DataTable();
using (var con = new SqlConnection(connectionString))
using (var da = new SqlDataAdapter(sql, con))
    da.Fill(table);

if(table.Rows.Count > 0)
{
    intequals = table.AsEnumerable().Where(r => r.Field<int>("Levenshtein") == 0).Count();
    if(equals > 0)
        Lab0.Text = string.Format("{0} equal found in database.", equals);
    else
    {
        int similars = table.AsEnumerable()
            .Where(r => r.Field<int>("Levenshtein") != 0)
            .Count();
        Lab0.Text = string.Format("{0} similar found in database.", similars);
    }
}
else
    Lab0.Text = "No equal or similar found in database!";