DataTable Select - Undefined function error

[ArunDhaJ]

Hi,
I've to filter rows from DataTable Select

There is a column named "Phone" which contains values in the following
format:
(901) 789 1234<BR>(901) 789 1235<BR>(901) 789 1221

I need to filter based on the phone number. the search criteria could
be 9017891221 or 891221.
this string should filter the above column

I've tried in SQL be replacing the '(', ')' and spaces as
9017891234<BR>9017891235<BR>9017891221
and performed search. It worked fine..

When i tried in Select method gives Unknown Function
REPLACE() error

I've used
REPLACE(REPLACE(REPLACE(Phones, ' ', ''), '(' , ''), ')', '') like
'%891221%'


Is there any way to achieve the same to filter the data?


Thanks in Advance
- ArunDhaJ

 

[Pavel Minaev]

Hi,
I've to filter rows from DataTable Select

There is a column named "Phone" which contains values in the following
format:
(901) 789 1234<BR>(901) 789 1235<BR>(901) 789 1221

I need to filter based on the phone number. the search criteria could
be 9017891221 or  891221.
this string should filter the above column

I've tried in SQL be replacing the '(', ')' and spaces as
9017891234<BR>9017891235<BR>9017891221
and performed search. It worked fine..

When i tried in Select method gives Unknown Function
REPLACE() error

I've used
REPLACE(REPLACE(REPLACE(Phones, ' ', ''), '(' , ''), ')', '') like
'%891221%'

Is there any way to achieve the same to filter the data?

Unfortunately, the syntax of the filter expression for
DataTable.Select() does not provide for any equivalent to REPLACE.
However, in .NET 3.5, you may use LINQ to DataSet to query, and
regexes to clean up the string:

DataTable dt = ...;
Regex insignificantCharacters = new Regex("[() ]");
IEnumerable<DataRow> result =
from row in dt.AsEnumerable()
let phones = row.Field<string>("Phone").Split(new[] {"<BR>"},
StringSplitOptions.None).Select(phone =>
insignificantCharacters.Replace(phone, ""))
where phones.Contains("891221")
select row;

In .NET 2.0, you'll have to resort to writing the same thing manually
using foreach.

 

[ArunDhaJ]

Hi,
would this operation in foreach loop would hit the performance?

manually looping in DataTable rows and removing those rows that doesnt
match may hit performance right?

-ArunDhaJ

 

[Pavel Minaev]

Hi,
would this operation in foreach loop would hit the performance?

manually looping in DataTable rows and removing those rows that doesnt
match may hit performance right?

-ArunDhaJ

Not very likely. DataTable is not a proper indexed data store, so
Select() will, most likely, just use foreach internally. If you need
to filter large data sets, you should use a proper relational
database, and do the filtering in SQL requests to that.

 

[ArunDhaJ]

Thanks Pavel, I've implemented using foreach and it works fine now.

Sorry for delayed response...

-ArunDhaJ