检查是否在数据库中存在类似的价值 [英] Check if similar value exists in database
问题描述
我要检查是否相同或相似的价值存在于数据库中。
我建立这个code:
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";
}
和它返回真正
。现在我想回到真正
如果字符串
在文本框中输入
输入错误的,但类似。
例如,如果在数据库中的值是 ASDFG
,我把文本框
ASDFH
或 AXDFG
它也必须返回真正
。
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
.
推荐答案
您可以使用T-SQL一个Levenshtein距离算法。例如(从这里):
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
现在是这样工作的:
SELECT Kod, Levenshtein = dbo.Levenshtein(Kod, @name, 2)
FROM SMS_KOD
WHERE dbo.Levenshtein(Kod, @name, 2) <> -1
您可以使用的SqlDataAdapter
来填补数据表
。如果它包含行至少有相似的 KOD
秒。
You can use a SqlDataAdapter
to fill a DataTable
. If it contains rows there are at least similar Kod
s.
下面是一个可能实现:
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!";
这篇关于检查是否在数据库中存在类似的价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!