«

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.Select("REPLACE(REPLACE(REPLACE(Phones, ' ', ''), '(' , ''),
')', '') like '%7891221%'")

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

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

Thanks in Advance
-ArunDhaJ

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/libr...xpression.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.Select("REPLACE(REPLACE(REPLACE(Phones, ' ', ''), '(' , ''),
> ')', '') like '%7891221%'")
>
> Actually I'm replacing all formating strings from the field,
> performing search from formatted string as converted as below:
> 9017891234<BR>9017891235<BR>9017891221.
>
> Please suggest me if i've done wrong or provide me any alternate for
> Replace function.
>
> Thanks in Advance
> -ArunDhaJ
>

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