DataColumn Select and DataView Filter Expression Problem

[David Hirschfeld]

I am trying to create a user enterable filter for a
datagrid that has a little intelligence. I would like
the filter to be capable of the following type of matches:

For this application, the datagrid displays a list of
Parts with PartID as the key. An example of a PartID is
12345-B11. When a user enters a filter value in an
attempt to reduce the datagrid to similar PartIDs they
may enter the PartID as 12345B11. How do I enter an
DataView.RowFilter(expression) to accomplish this? The
obvious expression would be the following (which works
fine as a SQL Query), but the .NET Framework doesn't
support the SQL Replace function:

"Replace(PartID, '-', '') LIKE '%XXXXXX%'"

With Query Analyzer, this produces the desired result.
How do I accomplish this with a DataView? I would like
to avoid either 1) Adding a new column to the DataView
which stores the value = Replace(PartID, '-', '') or 2)
spin through the DataView copying the desired rows one at
a time to a new DataTable.

 

[Frank Oquendo]

The
obvious expression would be the following (which works
fine as a SQL Query), but the .NET Framework doesn't
support the SQL Replace function:

"Replace(PartID, '-', '') LIKE '%XXXXXX%'"

string sql = string.Format("PartId = '{0}'",
userSuppliedString.Replace("-", ""));
DataTable.DefaultView.RowFilter = sql;

BTW, if your part ids are stored with the dash, how are you going to get
a match on that?

--
There are 10 kinds of people. Those who understand binary and those who
don't.

http://code.acadx.com
(Pull the pin to reply)

 

[David Hirschfeld]

BTW, if your part ids are stored with the dash, how are > you going to

get a match on that?

That was my question. I already understand how to do the other.

 

[Frank Oquendo]

That was my question. I already understand how to do the other.

That wasn't very clear. In answer to your question, it would appear you
cannot do what are you after.

--
There are 10 kinds of people. Those who understand binary and those who
don't.

http://code.acadx.com
(Pull the pin to reply)

 

[Frank Oquendo]

What about this?

string[] tokens = partsID.Split("-".ToCharArray());
string sql = string.Format("PartID LIKE '{0}%{1}'", tokens[0],
tokens[1]);
myTable.DefaultView.RowFilter = sql;

--
There are 10 kinds of people. Those who understand binary and those who
don't.

http://code.acadx.com
(Pull the pin to reply)

 

[David Hirschfeld]

I don't think I can do what I want to do. I believe this is an
oversight with the Framework designers.

 

[Kevin Yu [MSFT]]

Hi David,

I think the simplest way to achieve this is to add a column which has wipe
off the '-' in the PartID. The other way is to write your own algorithm to
search for the matching. However, I think that might result in a decrease
of performance.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[David Hirschfeld]

Thanks. This is the only solution I have come up with.