How to prevent similar (duplicate) entries in SQL database?

Question

//CHECK IF COMPLETE DUPLICATE
query = "SELECT * FROM TBL_FLAVORS WHERE flavor_name = @flavor_name AND flavor_supplierid = @supplier_id";
                    using (SqlConnection con = new SqlConnection(connstring))
{
     con.Open();
     using (SqlCommand cmd = new SqlCommand(query, con))
     {
          SqlDataReader read;
          cmd.Parameters.AddWithValue("@flavor_name", txtFlavorName.Text.ToString());
          cmd.Parameters.AddWithValue("@supplier_id", supplierid);
          read = cmd.ExecuteReader();
          if (read.Read())
          {
                MessageBox.Show("This flavor for this supplier already exists.");
                return;
          }
          read.Close();
      }
} 

//CHECK IF ALMOST SIMILAR
//IF RECORD FOUND, RETURN
//OTHERWISE, PROCEED TO UPDATE/INSERT

query = "SELECT * FROM TBL_FLAVORS WHERE (flavor_name LIKE @flavor_name OR flavor_name LIKE @flavor_name2 OR FLAVOR_NAME LIKE @flavor_name3) AND flavor_supplierid = @supplier_id";
using (SqlConnection con = new SqlConnection(connstring))
{
     con.Open();

     using (SqlCommand cmd = new SqlCommand(query, con))
     {
           string flavorname = "%" + txtFlavorName.Text.ToString()+ "%";
           string flavorname2 = "" + txtFlavorName.Text.ToString() + "%";
           string flavorname3 = "%" + txtFlavorName.Text.ToString() + "";
           SqlDataReader read;
           cmd.Parameters.AddWithValue("@flavor_name", flavorname);
           cmd.Parameters.AddWithValue("@flavor_name2", flavorname2);
           cmd.Parameters.AddWithValue("@flavor_name3", flavorname3);
           cmd.Parameters.AddWithValue("@supplier_id", supplierid);
           read = cmd.ExecuteReader();
           if (read.Read())
           {
                 DialogResult dialog = MessageBox.Show("It is possible that this flavor for this supplier already exists. Do you want to continue?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                 if (dialog == DialogResult.No)
                 {
                       return;
                 }
            }
            read.Close();
      }
}

The panelist asked what if there was already an 'inch' and the user entered an 'innch.' It should be recognized as a duplicate. Or say there is a 'chocolate' in the database, it should ask for confirmation if the user enters 'chocolates'.

Answer 1

When I had a similar request I decided to use Jaro–Winkler distance to measure the similarity between two strings.

I implemented it in C# and created a CLR function in SQL Server. See Jaro–Winkler distance algorithm in C# for example.

In my case I wanted to prevent users from entering the same company name several times, so when the user wants to create a new company he has to type the name of the new company first and then the program searches among existing companies in the database and shows found results ordered by the "relevance", which is Jaro–Winkler distance. It helps user to pick one of the existing entries instead of creating a duplicate even if the name is entered with some typos.

Answer 2

You will need to implement some approximate string matching algorithm that is a little smarter than what you have with the "LIKE" sql statements to be able to detect a similar word that has a character in between letters.

E.g. 'INCHX' will return TRUE if you execute with LIKE '%INCH%' but 'INNCH' will not match anything.

By the way, parameter flavour_name_2 and flavour_name_3 are probably redundant as flavour_name with % on both sides will match words with extra character at both start and end.

Approximate string matching: Approximate string matching algorithms