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.
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
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(")");
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%'"
