检查是否在数据库中存在类似的值[关闭](Check if similar value exists

我想检查是否有相同或类似的值存在于数据库中。 我建立这个代码：

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";
}

并返回true 。 现在我想回到true ，如果一个string上输入TextBox输入错误的，但类似。 例如，如果在数据库中的值是ASDFG和我把TextBox ASDFH或AXDFG它也必须返回true 。

Answer 1:

你可以使用T-SQL一个Levenshtein距离算法。 例如（来自这里 ）：

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

现在，像这样的工作：

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

您可以使用SqlDataAdapter填充DataTable 。 如果它包含行至少有相似的Kod秒。

SQL的小提琴

这里是一个可能的实现：

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!";

文章来源: Check if similar value exists in database [closed]