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.

score 2 · Answer 1 · answered Nov 25 '08 at 23:30

2

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

answered Nov 25 '08 at 23:30

Jimmy

89,068
17
119
137

Sadly, this is for something that's taking input in the form of SQL statements, not for an actual database. – Merus Nov 25 '08 at 23:48

Dustin · Answer 2 · 2008-11-25T23:33:42.367

0

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

edited Nov 25 '08 at 23:33

answered Nov 25 '08 at 23:20

Dustin

89,080
21
111
133

score 0 · Answer 3 · answered Nov 25 '08 at 23:23

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

Building a Query From a List of Strings

3 Answers3