Using Replace function in DataTable.Select

Discussion in 'ASP .Net' started by ArunDhaJ, Jul 3, 2008.

  1. ArunDhaJ

    ArunDhaJ Guest

    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
     
    ArunDhaJ, Jul 3, 2008
    #1
    1. Advertising

  2. ArunDhaJ

    bruce barker Guest

    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.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
    >
     
    bruce barker, Jul 3, 2008
    #2
    1. Advertising

  3. ArunDhaJ

    ArunDhaJ Guest

    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
     
    ArunDhaJ, Jul 4, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. J. Babe
    Replies:
    1
    Views:
    8,384
    Kevin Spencer
    Aug 15, 2003
  2. Fredrik Rodin

    Sort a datatable and create a new datatable

    Fredrik Rodin, Feb 16, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    9,085
    paul.mariotti
    Sep 28, 2007
  3. =?Utf-8?B?Qmx1ZWZsb3dlcg==?=

    Casting DataTable to class inherit from DataTable

    =?Utf-8?B?Qmx1ZWZsb3dlcg==?=, Apr 2, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    1,012
    =?Utf-8?B?Qmx1ZWZsb3dlcg==?=
    Apr 2, 2004
  4. jg
    Replies:
    1
    Views:
    744
    =?Utf-8?B?U2hhaWxlc2ggSmFubnU=?=
    Aug 17, 2004
  5. Ersin Gençtürk
    Replies:
    1
    Views:
    3,527
    Ersin Gençtürk
    Oct 6, 2004
Loading...

Share This Page