Compare texts string in VB. NET

[Chong]

I need help on comparing a word or phrase enter by a user at run time to a MS Access database field format as memo. The Access field contains around a thousand characters of words and/or phrases and I need to compare if there is any match with any of the phrases or words in the memo field of the mdb database. The comparison doesn't have to be exactly match the case, space, order, or the full word or phrase. A great example of what I want to do is the search tool in Windows 2000 when you click Start/Search/For Files or Folders... But in this case I want to compare to a string variable that store the contents of the memo field from my MS Access mdb file.

 

If anyone has any idea, please give me a helping hand on this issue.

 

Many thanks in advance!

 

Chong

[Nerseus]

Assuming your search words are 'dan' and 'jones' and you're searching on a field named 'memotext', here's what the SQL should look like (use a DataReader or a DataSet/DataAdapter to run the SQL):

SELECT * FROM Table1 WHERE memotext LIKE '*dan*' AND memotext LIKE '*jones*'

 

This would assume you want to find records that have BOTH 'dan' and 'jones'. If you want either word, use 'OR' instead of 'AND' above.

 

-Nerseus

[Chong]

Thanks! How would I do the same thing but not in the query? What if I already store the memotext into a string variable, strMemo, how would compare that?

 

Chong

[Nerseus]

Do you mean a single string variable? You can use the IndexOf method:

       Dim s As String
       s = "hello world this is dan jones and I love .NET"

       If s.IndexOf("dan") > 0 And s.IndexOf("jones") > 0 Then
           Debug.WriteLine("Found")
       Else
           Debug.WriteLine("Not Found")
       End If

[Chong]

Will the comparison in this code evaluate casesensitivity? I don't want to evaluate casesensitivity and it doesn't have to match the whole word or phrase. For example:

Dim UserString as String

Dim CompareString as String

 

CompareString = Technology

UserString = Tech

When CompareString and UserString are being compare, I want the result to be true.

 

Chong

[Nerseus]

IndexOf is case-sensitive. I think you'll have to code that type of "in string" comparison yourself, unless VB.NET supports the same InStr function as VB6 along with VB6's "Option Compare Text". I'm not that familiar with VB.NET...

 

If you can't do the query in SQL on the database, can you do it against a column in a DataSet? The DataSet's DataTable supports a Select statement that can do the above type of searching client-side. For example (couldn't test this in VB.NET - no sample DataSet projects at the moment):

DataRow[] rows = myDataSet.Tables["Table1"].Select("memotext LIKE '%dan%' AND memotext LIKE '%jones%'");
if(rows.Length > 0)
Debug.WriteLine("found");
else
Debug.WriteLine("not found");

 

-Nerseus

Edited April 16, 2003 by Nerseus

[Chong]

Thanks! I'll give that a try and see what happens.

 

Chong

:-\

[Volte]

To do a case insensitive IndexOf, you could try this:

myString.ToUpper.IndexOf(searchForThis.ToUpper)

:-\

[Chong]

Thanks! I'll give that a try.

 

Chong