Help with search a comma delimited db text field using an string array!

Question

I have a db
field that contains a comma delimited text field with a list of keywords(ie.
MAT,MDT,PUR), this field can contain one to many different keywords separated
by commas. On my form I have a listbox that allows the user to select one or
more of these keywords. What I'm looking for is a way to search the db for
records that contain at least one of the selected keywords in the listbox in
the text field. <o:p></o:p>

Right now i
use:<o:p></o:p>

var kw = from item in lstKeywords.SelectedItems.Cast<string>() select item ; <o:p></o:p>

The following will only
work if there is only one Keyword in the db text field, not multiple separated
by commas.<o:p></o:p>

var b = from n in BEMentities.tickets where (kw.Contains(n.Keywords) select n;<o:p></o:p>

What I'm looking for is
something like, though won't work of course<o:p></o:p>

var b = from n in BEMentities.tickets where (kw.Contains(n.Keywords.split(',')) select n;<o:p></o:p>

Any assistance would be
much appreciated,<o:p></o:p>

Mike<o:p></o:p>

Answer 1

Hi MiLambert;

I believe you will find this to work for you.

var b = from n in BEMentities.tickets
        from word in kw
        where n.Keywords.Contains(word)
        select n;

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

Thanks Fernando,

Worked great!  Appreciated it.

Now for a bonus question;) 

What if I only wanted to return records that only had all the selected keywords.  ie - listbox selection = MDT/MAT.  I want to return only records that look like this- MAT,MDT or MAT,PST,MDT but not MDT, or MDT,PST or MAT,PST, etc.

Thanks again for your help!!!

Mike

Answer 3

Hi Mike;

Well this had my mental juices going. The following code snippet is about the only way I could figure out how to do it.

// List of keywords from the ListBox
List<string> kw = new List<string>() { "MDT","MAT"};
// StringBuilder object used to create a Regex pattern
StringBuilder sbKW = new StringBuilder();
// Build the Regex pattern of all the keywords
foreach (var keyword in kw)
{
    if( sbKW.Length == 0 )
    {
        sbKW.Append( keyword );
    }
    else
    {
        sbKW.Append( "|" + keyword );
    }
}
// Finish building the pattern by placeing ( ) around the string
sbKW.Insert( 0, "(" );
sbKW.Append( ")" );

// Query the database for all Tickets.Keywords that has at least one of 
// the words you are looking for.
var dbResults = ( from t in BEMentities.Tickets
                  from word in kw
                  where t.Keywords.Contains( word )
                  select t ).Distinct( ).ToList( );

// Now from the results of the db query select only those that have 
// all the words in the Keyword field
var hasAll = ( from a in dbResults
               where Regex.Matches( a.Keywords, sbKW.ToString() ).Count == kw.Count
               select a.Keywords ).ToList( );

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 4

Thanks Fernando for all your help!

Answer 5

  

Not a problem, glad to help.

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".