By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,419 Members | 1,432 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,419 IT Pros & Developers. It's quick & easy.

DataTable Select - Undefined function error

P: n/a
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
Jul 4 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Jul 4, 6:50*pm, ArunDhaJ <arund...@gmail.comwrote:
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<DataRowresult =
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.
Jul 4 '08 #2

P: n/a
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
Jul 5 '08 #3

P: n/a
On Jul 5, 8:53*am, ArunDhaJ <arund...@gmail.comwrote:
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.
Jul 5 '08 #4

P: n/a
Thanks Pavel, I've implemented using foreach and it works fine now.

Sorry for delayed response... ;)

-ArunDhaJ
Jul 8 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.