这个查询有什么问题?或代码
本文关键字:代码 问题 什么 查询 | 更新日期: 2023-09-27 17:52:52
用户输入如下搜索字符*"blue,yellow green,red pink,stack over flow"*现在我想把它放入数组中,并使用以下代码
搜索它string[] search = mysearch.Split(',');
List<Result> myresult = new List<Result>();
for (int kk = 1; kk < search.Length; kk++)
{
where += " And '%" + search[kk] + "%'";
OleDbCommand sqlcmdCommand0 = new OleDbCommand("select Distinct name from table1 where search like '%" + search[0] + "%' " + where + " order by name", myCon);
sqlcmdCommand0.CommandType = CommandType.Text;
OleDbDataReader sda = sqlcmdCommand0.ExecuteReader();
while(sda.read())
{
myresult.name= sda.getString(0);
Result.add(myresult);
}
}
return Result;
查询如下:
select Distinct name from table1 where search like '%blue%' And '%yellow%' And '%Green %' order by name
,它应该是这样的:
select Distinct name from table1 where search like '%blue%' And '%yellow Green %' order by name
但问题是它把字符串分隔在空格之后而不是逗号,我想把字符串放到数组中逗号之后而不是字符之间的空格之后
好的,我想你可能只是想扔掉你当前的代码…下面是生成WHERE
条件所需的操作:
string[] searchTerms = mySearch.Split(',');
StringBuilder conditions = new StringBuilder();
foreach(string term in searchTerms)
{
if (conditions.Length > 0)
conditions.Append(" AND ");
conditions.AppendFormat("search LIKE '%{0}%'", term.Replace("'", "''"));
}
string query = "select Distinct name from table1";
if (conditions.Length > 0)
query = query + " WHERE " + conditions;
您的SQL是不正确的,您需要一个like
操作符为每个比较,并为每个操作符两个操作数。此外,您可能希望在比较之间使用or
运算符,否则您将只找到包含所有颜色的记录,而不是任何颜色:
select Distinct name
from table1
where
search like '%blue%' or
search like '%yellow%' or
search like '%green%'
order by name
如果您希望它分割逗号和空格,请使用带有params参数的重载,并传递一个逗号字符和一个空白字符:
"red, blue green".Split(',', ' ');