471,338 Members | 1,028 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,338 software developers and data experts.

DataTable Select - Undefined function error

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
4 4000
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
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
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
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.

Similar topics

4 posts views Thread by bobsawyer | last post: by
9 posts views Thread by Frank | last post: by
reply views Thread by Jason MacKenzie | last post: by
reply views Thread by Chris Ericoli | last post: by
7 posts views Thread by wk6pack | last post: by
10 posts views Thread by D. Shane Fowlkes | last post: by
9 posts views Thread by jsoques | last post: by
6 posts views Thread by fniles | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.