Using Replace function in DataTable.Select

[ArunDhaJ]

Hi,
I'm having a DataTable with a column named "Phones" having value like

(901) 789 1234<BR>(901) 789 1235<BR>(901) 789 1221

I need to perform filtering based on the phone number of the form
"9017891221 " or "7891221"

The following query works fine when tested in SQL but fails saying
that it doesnt support REPLACE() function

DataTable.Selec t("REPLACE(REPL ACE(REPLACE(Pho nes, ' ', ''), '(' , ''),
')', '') like '%7891221%'")

Actually I'm replacing all formating strings from the field,
performing search from formatted string as converted as below:
9017891234<BR>9 017891235<BR>90 17891221.

Please suggest me if i've done wrong or provide me any alternate for
Replace function.

Thanks in Advance
-ArunDhaJ

[=?Utf-8?B?YnJ1Y2UgYmFya2Vy?=]

RE: Using Replace function in DataTable.Selec t

as y9ou are querying a datatable, not sql, you need to use a valid query.
here are all the column filters you can use:

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

you could do the replace in sql before loading the table, or use linq, and
write a .net function to do the logic.

-- bruce (sqlwork.com)


"ArunDhaJ" wrote:

Hi,
I'm having a DataTable with a column named "Phones" having value like
>
(901) 789 1234<BR>(901) 789 1235<BR>(901) 789 1221
>
I need to perform filtering based on the phone number of the form
"9017891221 " or "7891221"
>
The following query works fine when tested in SQL but fails saying
that it doesnt support REPLACE() function
>
DataTable.Selec t("REPLACE(REPL ACE(REPLACE(Pho nes, ' ', ''), '(' , ''),
')', '') like '%7891221%'")
>
Actually I'm replacing all formating strings from the field,
performing search from formatted string as converted as below:
9017891234<BR>9 017891235<BR>90 17891221.
>
Please suggest me if i've done wrong or provide me any alternate for
Replace function.
>
Thanks in Advance
-ArunDhaJ
>

[ArunDhaJ]

Re: Using Replace function in DataTable.Selec t

Hi Bruce,
Actually I'm not loading from the table, but from the XML which i
receive in this format. So I've no other option filtering here. If it
doesnt support Replace() is there any other way to perform such
operation. I hvent used linq.

-ArunDhaJ