• Article

Word and Character Matching

Topic Last Modified: 2006-06-12

You can find items based on word and character matches of a specified search string. The example shows how to construct a Structured Query Language (SQL) query using the predicates in the following table. The results of the search in this example are passed to the DoResults function in Enumerating Search Results.

Predicate Usage

CONTAINS Predicate

Finds words in the order specified, such as "Romeo and Juliet."

FREETEXT Predicate

Finds one or more words specified, such as "apples oranges pears."

FORMSOF Predicate

Finds word variations, such as "gardening" and "garden."

LIKE Predicate

Finds characters that match a specified string. The percent character (%) matches any or no characters adjacent to the specified string. For example, "%he ca%" matches "the cat."

Example

VBScript

Example

'Finds items based on CONTAINS, FREETEXT, FORMS, or LIKE predicate
'This example uses FREETEXT to find reports with names of cities
'Passes search result recordset to DoResults (see Enumerating Results)

On Error GoTo ErrHandler

Const adErrNoCurrentRecord = 3021
Dim info
Dim infoNT
Dim cName
Dim dName
Dim Conn
Dim cURL
Dim relURL
Dim searchspec
Dim predicate
Dim Rs
Dim strQ

'get computer and domain information
Set info   = CreateObject("ADSystemInfo")
Set infoNT = CreateObject("WinNTSystemInfo")
cName = infoNT.ComputerName
dName = info.DomainDNSName

'create connection object
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Exoledb.DataSource"

'URL for connection object
'is at the virtual directory root
cURL = "http://" & cName & "." & dName & "/" & "public"

Conn.Open cURL

'relative URL is the folder to search
relURL = "Reports/FieldOffices"

'search specification variable
searchspec = "Tokyo London Bogota Honolulu Cairo Sydney Chicago"

'predicate to use in search
predicate = "FREETEXT"

Set Rs = CreateObject("ADODB.Recordset")

' Construct the SQL query
strQ = "SELECT ""urn:httpmail:subject"" "
strQ = strQ & "FROM """ & relURL & """ "

'Construct a query based on the search predicate.
Select Case predicate
   Case "CONTAINS"
      strQ = strQ & "WHERE CONTAINS(*, '" & Chr(34) & searchspec & Chr(34) & "') "
   Case "FREETEXT"
      strQ = strQ & "WHERE FREETEXT(*, '" & Chr(34) & searchspec & Chr(34) & "') "
   Case "LIKE"
      strQ = strQ & "WHERE ""urn:schemas:httpmail:textdescription"" LIKE '" & searchspec & "'"
   Case "FORMSOF"
      strQ = strQ & "WHERE CONTAINS('FORMSOF(INFLECTIONAL," & Chr(34) & searchspec & Chr(34) & ")')"
   Case Else
      strQ = strQ & "WHERE CONTAINS(*, '" & Chr(34) & searchspec & Chr(34) & "') "
End Select

Rs.Open strQ, Conn

'If empty recordset, return error
'If successful call DoResults routine passing the recorset
If Rs.EOF = True Then
   Response.Write "No items found, run another query."
Else
   Response.Write "Success! Found " & Rs.RecordCount
   DoResults Rs
End If

GoTo Ending

' Implement custom error handling here.
ErrHandler:
   WScript.echo Err.Number + " " + Err.Description
   Err.Clear

Ending:

   Conn.Close
   Rs.Close

   Set Conn = Nothing
   Set Rs = Nothing