.Net Filter BindingSource

Question

If I filter a BindingSource's column like this: "column1 LIKE '%John%'", then all rows containing John on that column will return.

How to return all rows for which column1 contains [some text]John[some text]Doe ?

"column1 LIKE '%John%Doe'" does not work.

Answer 1

try this

   "ItemName LIKE '*john*' and  ItemName LIKE '*doe*'"

Answer 2

Per the documentation at http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

Wildcard Characters Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:

"ItemName LIKE 'product'"

"ItemName LIKE '*product'"

"ItemName LIKE 'product*'"

Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.

Therefore, you can't use LIKE to do what you want. Your next best bet is to get the string representation and use a Regex to parse it looking for the text you want. That's going to be SLOW.

You COULD do

column1 LIKE '%John%' AND LIKE %Doe%'

but the logic is different and may not be what you really want.

Edit - added

You might be better off doing the filtering at the server level, as your DB might support a wildcard in the middle of the string. I just tried it on our SQL Server and it works fine. (SQL Server 2005)