none
Whole Word Search using DataView.RowFilter? RRS feed

  • Question

  • I've been looking for a week now to find how to simply search for whole words using DataView.RowFilter.  No luck (or I wouldn't be making this post).  Surely it should be simple!

    I have a Keywords column.  Data might be something like:

    Row 1: "City; Cosmopolitan"
    Row 2: "Star; Cosmo; Galaxy"

    I want to search for a whole word, "Cosmo".  In MySql the query would be:

    SELECT *
    FROM table
    WHERE Keywords REGEXP '[[:<:]]Cosmo[[:>:]]'

    Any thoughts on how to do this (a whole word search) using DataView.RowFilter?

    Best regards,
    Glenn


    Sunday, August 14, 2011 2:25 PM

Answers

  • Something like this should work:

    string Search = " Cosmo;";
    dv.RowFilter = "Keyword LIKE '%" + Search + "%'";
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, August 14, 2011 4:22 PM
  • So the data in your Rows has no consistent delimiter? I assumed from your example that a word would start with a space and end with a semi-colon.I doubt if you can use a RexEx in the RowFilter string ... I'm pretty sure only a "LIKE" will work.

    Is there at least a known list of possible delimiters? If so, I suppose your filter could contain all of those possible delimiters, something like this:

    string Search = "Cosmo";
    dv.RowFilter = string.Format("Keyword LIKE '% {0} %' OR Keyword LIKE '% {0};%' OR Keyworkd LIKE '% {0},%'", Search);
    
    


    I know, it's not very elegant. =0( But off the top of my head, I'm not coming up with anything better. Sorry.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, August 14, 2011 4:44 PM
  • Using LINQ, something like this would likely do the trick for you ...

    var yourCollection = from r in dt.Rows.Cast<DataRow>()
    
          where (";" + ((string)r["Keyword"]).Replace(",", ";").Replace(".", ";").Replace("; ", ";") + ";").Contains(";" + searchWord + ";")
    
          select r;
    
    
    
    


    The thought is to replace all the possible delimiters with a single delimiter, then look for the delimited searchWord in that text.

    {note: air code!}


    Brent Spaulding | Access MVP
    Sunday, August 14, 2011 10:05 PM
  • Its important to remeber that .RowFilter is not the same as creating a WHERE clause of a SQL Statement.  With WHERE clause presented to a database engine, you have a more powerful set of inherent functions to call upon, where as RowFilter seems somewhat limited in that regard.  For that reason, my first inclination (remember I am novice in the C# world :) ). I would suggest that you fill your DataTable with a column that utilizes a REPLACE() T-SQL function call that performs the task of the Replace() I illustrated with the Linq expression, then use a RowFilter setting to narrow your results.

    DataTable dt = new DataTable();
    string sSQL = @"SELECT someTable.*" 
              + ", ';' + REPLACE(REPLACE(REPLACE(someField,',',';'),' ',';'),'.',';') + ';' As nSomeField"
              + " FROM someTable";
          
    using (SqlDataAdapter da = new SqlDataAdapter(sSQL, "someConnectionString"))
    {
      da.Fill(dt);
    }
    
    DataView dv = dt.DefaultView;
    
    dv.RowFilter = "nSomeField LIKE '%;'" + yourKeyWordVariable + "';%'";
    
    


    Another alternative would be add a just to use a WHERE clause when filling a DataTable, then use the .DefaultView of that DataTable.

    There are other alternatives to, like creating a DataColumn and setting the .Expression property, then appending it to your DataTable, then you can filter on that new column with .RowFilter of your DataView.


    Brent Spaulding | Access MVP
    Saturday, August 27, 2011 1:39 PM

All replies

  • Something like this should work:

    string Search = " Cosmo;";
    dv.RowFilter = "Keyword LIKE '%" + Search + "%'";
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, August 14, 2011 4:22 PM
  • Hi Bonnie,

     

    That would work for the very specific example I gave, but how about a generic whole word search?  "Cosmo" could have been in there as "; Cosmo;", "Cosmo Fan", ",Cosmo,Galaxy,", etc.

     

    Cheers,

    Glenn

    Sunday, August 14, 2011 4:26 PM
  • So the data in your Rows has no consistent delimiter? I assumed from your example that a word would start with a space and end with a semi-colon.I doubt if you can use a RexEx in the RowFilter string ... I'm pretty sure only a "LIKE" will work.

    Is there at least a known list of possible delimiters? If so, I suppose your filter could contain all of those possible delimiters, something like this:

    string Search = "Cosmo";
    dv.RowFilter = string.Format("Keyword LIKE '% {0} %' OR Keyword LIKE '% {0};%' OR Keyworkd LIKE '% {0},%'", Search);
    
    


    I know, it's not very elegant. =0( But off the top of my head, I'm not coming up with anything better. Sorry.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, August 14, 2011 4:44 PM
  • Even with only four possible delimiters (" ;,."), the number of like combinations would be large (well, 16 in this case). :(  Doable I suppose as a slightly more than last resort!  But surely DataView has some way to do a whole word search.  All Sql implementations do.

    Thanks again,

    Glenn

    Sunday, August 14, 2011 5:08 PM
  • Glenn,

    Might be able to do something with LINQ ... I'm gonna play with that a bit right now and I'll get back to you ...


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, August 14, 2011 9:11 PM
  • Using LINQ, something like this would likely do the trick for you ...

    var yourCollection = from r in dt.Rows.Cast<DataRow>()
    
          where (";" + ((string)r["Keyword"]).Replace(",", ";").Replace(".", ";").Replace("; ", ";") + ";").Contains(";" + searchWord + ";")
    
          select r;
    
    
    
    


    The thought is to replace all the possible delimiters with a single delimiter, then look for the delimited searchWord in that text.

    {note: air code!}


    Brent Spaulding | Access MVP
    Sunday, August 14, 2011 10:05 PM
  • IF you use a CollectionView you canb creat a custom filer with the predicate function:

    http://stackoverflow.com/questions/1813450/any-mean-to-filter-dataview-with-a-custom-function

    That is what I did with my dataset anyway


    Kenneth
    Sunday, August 14, 2011 10:21 PM
  • Brent's suggestion looks promising (I haven't actually tried it though). Then you'd also want to use the .CopyToTable() and use the DefaultView of the resulting DataTable, since you want to use a DataView.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, August 14, 2011 10:44 PM
  • Hi,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day. 


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 26, 2011 3:10 AM
    Moderator
  • Alan,

     

    I'm just seeing Brent's latest suggestion now.  For some reason I don't get email notifications of responses.  Since I'm using .Net 2.0 I don't think a Linq solution would work (intro'd in 3.5 I believe).

    I'm surprised nobody else has ever wanted to do a whole word search for a given column using DataView.SetFilter.  Most Sql system supports a simple mechanism, such as this below which works in MySql:

    SELECT *

    FROM table

    WHERE Keywords REGEXP '[[:<:]]Cosmo[[:>:]]'

     

    I just can't believe I may have to give up on this. :(

     

    Cheers,

    Glenn

    Saturday, August 27, 2011 12:55 AM
  • Hi Glenn,

    For some reason I don't get email notifications of responses

    Notifications seem to get broken periodically. =0(

    Did you happen to look at the stackoverflow link provided above by Kenneth? There seem to be some promising suggestions and workarounds there. Check it out ...


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, August 27, 2011 1:06 AM
  • Its important to remeber that .RowFilter is not the same as creating a WHERE clause of a SQL Statement.  With WHERE clause presented to a database engine, you have a more powerful set of inherent functions to call upon, where as RowFilter seems somewhat limited in that regard.  For that reason, my first inclination (remember I am novice in the C# world :) ). I would suggest that you fill your DataTable with a column that utilizes a REPLACE() T-SQL function call that performs the task of the Replace() I illustrated with the Linq expression, then use a RowFilter setting to narrow your results.

    DataTable dt = new DataTable();
    string sSQL = @"SELECT someTable.*" 
              + ", ';' + REPLACE(REPLACE(REPLACE(someField,',',';'),' ',';'),'.',';') + ';' As nSomeField"
              + " FROM someTable";
          
    using (SqlDataAdapter da = new SqlDataAdapter(sSQL, "someConnectionString"))
    {
      da.Fill(dt);
    }
    
    DataView dv = dt.DefaultView;
    
    dv.RowFilter = "nSomeField LIKE '%;'" + yourKeyWordVariable + "';%'";
    
    


    Another alternative would be add a just to use a WHERE clause when filling a DataTable, then use the .DefaultView of that DataTable.

    There are other alternatives to, like creating a DataColumn and setting the .Expression property, then appending it to your DataTable, then you can filter on that new column with .RowFilter of your DataView.


    Brent Spaulding | Access MVP
    Saturday, August 27, 2011 1:39 PM