DataTable Select: Expression with a space problem...

Question

Situation:

Hello! I have a little problem in a C# project. I am using the Select method from a DataTable object and using an expression to get what I want but I am having some trouble with a space in one of the strings I am using for the expression.

So here is a code sample of what I have:

DataTable table;
//...  
DataRow[] rows = table.Select("[" + columnNameStr + "]" + " LIKE '*" + searchStr + "*'");
//...

The string searchStr can have a white space in it. So for example, if you have a name such as Bob Dude in searchStr and the string should be Dude Bob instead the select expression will not return any result.

---

Question:

What is the expression I need to use if I want to get a result when the words in searchStr are not necessarily in the right order to get a result?

Answer 1

Split your search string on spaces, and then build your expression string in a loop using OR. Something along these lines might work for you:

var searchStr = "Bob Dude";
var splitSearchString = searchStr.Split(' ');
var columnNameStr = "Name";
var expression = new List<string>();
DataTable table = new DataTable();
//...  
foreach (var searchElement in splitSearchString)
{
    expression.Add(
        string.Format("[{0}] LIKE '*{1}*'", columnNameStr, searchElement));
}
var searchExpressionString = string.Join(" OR ", expression.ToArray());
DataRow[] rows = table.Select(searchExpressionString);

Answer 2

another simpler way worked for me.

Say the searchStr is "Hello World".

DataTable.Select("ColumnName" + "'" + searchStr + "'");

the key is add a single quote (') before & after the search string.