Building a Query From a List of Strings

Question

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?

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

([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.

Answer 1

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%'"

or

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

Answer 2

I'd use a StringBuilder and a for loop. Assuming your list is called "list" and is a 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(")");

Answer 3

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:

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

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