Creating a query from a list of strings
How would you take an arbitrary list of strings (of the form "% [text]%") and database columns and turn them into a SQL query that does a LIKE comparison for each row in the list?
Example: I have three lines in my list: "% bc%", "% def%" and "% ab%". This creates a request:
([ColumnName] LIKE "%bc" AND [ColumnName] LIKE "%def%") AND [ColumnName] LIKE "%ab%"
A C # example would be great, but feel free to write it in your language of choice.
0
source to share
3 answers
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 must use the Sql Server Full Text Search Tools. the request will look like this:
select * from table
where FREETEXT("bc def ab")
With correct indices, this should exceed the LIKE list
+2
source to share
It's just a concatenation of lines 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%'"
0
source to share
I would use 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(")");
0
source to share