Check if similar value exists in database

Question

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.ConnectionStrings["ChipstarALConn"].ToString();

string sql1 = "select Count(*) from SMS_KOD where KOD = @name";
SqlCommand cmd1 = new SqlCommand(sql1, con1);
cmd1.Parameters.AddWithValue("@name", TextBox1.Text);
con1.Open();

int result = (int)cmd1.ExecuteScalar();
if (result > 0)
{
    Lab0.Text = "true";
}
else
{
    Lab0.Text = "false";
}

And it returns true. Now I want to return true if a string entered on TextBox is entered wrong but similar. For example if a value in Database is ASDFG and I put on TextBox ASDFH or AXDFG it must also return true.

What is the criteria? Should it be true in any wrong value is entered, and false for correct ones, or do you have some other logic in mind? — SWeko, Dec 04 '14 at 11:49
If the value in the database is "ASDFG" and the user enters "AXDFG", in what way is that true, they don't match. Do you want use some kind of fuzzy matching algorithm? If so this is too broad for an SO question. — Ben Robinson, Dec 04 '14 at 11:49
I guess he wants string relatives, where one character doesn't violate equation. — İsmet Alkan, Dec 04 '14 at 11:50
What I want is that if a user puts a character wrong in a string it still must return true. — aldoblack, Dec 04 '14 at 11:52
Come on guys. :P Can I get any help? I got it to work by String.Replace method by comparing 2 strings from 2 Textboxes but I want now from Database. — aldoblack, Dec 04 '14 at 12:01
@StephenMuecke here's a list of numbers: 2 6 13 19 20 21 24 33 38 40. you need to select substrings of them. or you can refer to http://anastasiosyal.com/POST/2009/01/11/18.ASPX — İsmet Alkan, Dec 04 '14 at 12:07
you can traverse string till the string length. And if string does not match more than one character it will return false otherwise true. you can maintain a counter for that for the unmatched characters. — Rashedul.Rubel, Dec 04 '14 at 12:33
P.S. I found the right answer. You did nor have to put on hold. Mr. @Tim Schmelter answered it. And I marked his answer as the right answer. — aldoblack, Dec 04 '14 at 20:52

score 2 · Accepted Answer · edited May 23 '17 at 11:50

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

CREATE FUNCTION dbo.Levenshtein(@s nvarchar(4000), @t nvarchar(4000), @d int)
RETURNS int
AS
BEGIN
  DECLARE @sl int, @tl int, @i int, @j int, @sc nchar, @c int, @c1 int,
    @cv0 nvarchar(4000), @cv1 nvarchar(4000), @cmin int
  SELECT @sl = LEN(@s), @tl = LEN(@t), @cv1 = '', @j = 1, @i = 1, @c = 0
  WHILE @j <= @tl
    SELECT @cv1 = @cv1 + NCHAR(@j), @j = @j + 1
  WHILE @i <= @sl
  BEGIN
    SELECT @sc = SUBSTRING(@s, @i, 1), @c1 = @i, @c = @i, @cv0 = '', @j = 1, @cmin = 4000
    WHILE @j <= @tl
    BEGIN
      SET @c = @c + 1
      SET @c1 = @c1 - CASE WHEN @sc = SUBSTRING(@t, @j, 1) THEN 1 ELSE 0 END
      IF @c > @c1 SET @c = @c1
      SET @c1 = UNICODE(SUBSTRING(@cv1, @j, 1)) + 1
      IF @c > @c1 SET @c = @c1
      IF @c < @cmin SET @cmin = @c
      SELECT @cv0 = @cv0 + NCHAR(@c), @j = @j + 1
    END
    IF @cmin > @d BREAK
    SELECT @cv1 = @cv0, @i = @i + 1
  END
  RETURN CASE WHEN @cmin <= @d AND @c <= @d THEN @c ELSE -1 END
END
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)
{
    int equals = 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!";

Thank You. You've been helpful. Now I have to figure it out how to put it in my code above. If that's ok for you can you give me a hand? Thank You. — aldoblack, Dec 04 '14 at 12:14

Check if similar value exists in database

1 Answers1

Linked