Partial v. exact match

[emozley]

I have written something that allows people to search library catalogue by author, title and publisher.

I am trying to find a way of allowing the user to specify either a partial or exact match. So far the bit of the SQL statement that is built for the author search is as follows:

AuthorSearch is the string that gets added onto the overall SQL string. Author array is the words that are typed into the search box.

<%
AuthorSearch=""
If UBOUND(AuthorArray,1) >= 0 Then AuthorSearch=AuthorSearch & "("
For i=0 To UBOUND(AuthorArray,1)
AuthorSearch=AuthorSearch & "Catalogue.Author LIKE '%" & AuthorArray(i) & "%'"
If i<UBOUND(AuthorArray,1) Then
AuthorSearch=AuthorSearch & MatchType
End If
Next
If UBOUND(AuthorArray,1) >= 0 Then AuthorSearch=AuthorSearch & ")"

strSQL=strSQL & AuthorSearch
%>

Now I can use some logic to say if the user has specified they want a partial match then to use LIKE '%...%' and if it's an exact match to use ='...'

The problem is if there is an author called Adam Jones and you search for 'Adam' exact match it won't find it because the SQL turns into

...WHERE Author='Adam'

when in fact we need

...WHERE Author='Adam Jones'

What is the best way of matching a whole word but that whole word can appear anywhere in the field?

Thanks very much

Ed

 

[Sheco]

The following would give the result but I don't think "Adam" is really an exact match for "Adam Jones":
[tt]WHERE Author LIKE '%Adam%' [/tt]


PS: When I have this type of puzzle, I prefer to set the ASP aside and work/experiment directly with the database until I figure out which SQL text will give me the best results. Then I work backwards and write the ASP to build the proper SQL. This helps me mentally separate the issues so I can give each my full attention in turn.

 

[emozley]

Thanks for the advice Sheco after experimenting directly with the SQL it seems the best way to match whole words is to put a " " before and after the field you are searching and look for '% Adam %'

This is done with the following:

AuthorSearch=""
If UBOUND(AuthorArray,1) >= 0 Then AuthorSearch=AuthorSearch & "("
For i=0 To UBOUND(AuthorArray,1)
If ExactMatch=TRUE Then AuthorSearch=AuthorSearch & "' ' " & CHR(038)
AuthorSearch=AuthorSearch & " Catalogue.Author "
If ExactMatch=TRUE Then AuthorSearch=AuthorSearch & CHR(038) & " ' ' "
AuthorSearch=AuthorSearch & "LIKE "
AuthorSearch=AuthorSearch & " '%"
If ExactMatch=TRUE Then AuthorSearch=AuthorSearch & " "
AuthorSearch=AuthorSearch & AuthorArray(i)
If ExactMatch=TRUE Then AuthorSearch=AuthorSearch & " "
AuthorSearch=AuthorSearch & "%' "
If i<UBOUND(AuthorArray,1) Then
AuthorSearch=AuthorSearch & MatchType
End If
Next
If UBOUND(AuthorArray,1) >= 0 Then AuthorSearch=AuthorSearch & ")"

The problem now is that some Author fields have punctuation in. For example if a book is written by "Adam, Ben and Charles" and you execute the query...

SELECT Author FROM " " & "Adam, Ben and Charles" & " " WHERE Author LIKE '% Adam %'

...it won't find it because of the comma after Adam. What I need to do is replace the line

AuthorSearch=AuthorSearch & " Catalogue.Author "

With something that turns Catalogue.Author into Catalogue.Author but replaces . , ; ' etc. into spaces.

Is this possible - can a function be called from within a SQL statement? I am only using Access so I fear my options may be limited.