从字符串列表构建查询

How would you take an arbitrary list of strings (of the form "%[text]%") and a database column, and turn them into a SQL query that does a LIKE comparison for each string in the list?

您将如何获取任意字符串列表(格式为“%[text]%”)和数据库列,并将它们转换为SQL查询,为列表中的每个字符串执行LIKE比较?

An example: I have three strings in my list, "%bc%", "%def%" and "%ab%". This builds the query:

例如:我的列表中有三个字符串,“%bc%”,“%def%”和“%ab%”。这构建了查询:

([ColumnName] LIKE "%bc" AND [ColumnName] LIKE "%def%") AND [ColumnName] LIKE "%ab%"

A C# example would be excellent, but feel free to write it in the language of your choice.

C#示例非常好,但您可以使用您选择的语言编写它。

3 个解决方案

#1

To answer your question directly,

要直接回答你的问题,

string.join(" and ", 
    (new[] { "%bc%", "%def%", "%ab%" })
    .Select(x => string.Format("[{0}] LIKE '{1}'",columnName, x))
    .ToArray());

To solve your problem, you should use the Sql Server full-text search tools. the query would be:

要解决您的问题,您应该使用Sql Server全文搜索工具。查询将是:

select * from table
where FREETEXT("bc def ab")

With the correct indices, this should outperform a list of LIKEs

使用正确的索引,这应该优于LIKE列表

#2

I'd use a StringBuilder and a for loop. Assuming your list is called "list" and is a List:

我使用StringBuilder和for循环。假设您的列表名为“list”并且是List:


StringBuilder sql = new StringBuilder();
if (list.Count > 0)
    sql.AppendFormat(CultureInfo.InvariantCulture, "([{0}] LIKE \"{1}\"", columnName, list[0]);

for (int i = 1; i < list.Count; i++)
{
    sql.AppendFormat(CultureInfo.InvariantCulture, " AND [{0}] LIKE \"{1}\"", columnName, list[i]);
}

if (list.Count > 0)
    sql.Append(")");

#3

It's just a string join on a map:

它只是地图上的字符串连接:

>>> los=['ab', 'cd', 'ef']
>>> ' and '.join(("somecolumn like '%%%s%%'" % s) for s in los)
"somecolumn like '%ab%' and somecolumn like '%cd%' and somecolumn like '%ef%'"

>>> ' and '.join(("somecolumn like '%" + s + "%'") for s in los)
"somecolumn like '%ab%' and somecolumn like '%cd%' and somecolumn like '%ef%'"

#1