How do you use full text search without requiring exact terms?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike - 21 Jun 2008 11:32 GMT

Hello,

Right now I have a full text search going like this:

SELECT top 25 id, question, answer from [answers] WHERE
CONTAINS(question, '""" & search & """')

where "search" is the variable holding the form value the person typed
in.

the problem is, it only finds exact matches. For example, if williams
does not match william's, william does not match williams, etc

is there a way to make the search more useful?

Thanks for any help!

-Mike

Reply to this Message

PW - 22 Jun 2008 04:05 GMT

> SELECT top 25 id, question, answer from [answers] WHERE
> CONTAINS(question, '""" & search & """')

You need to use the LIKE keyword and put wildcard markers on both sides of
the "search" variable.

So in ACCESS it would be like this ...
SELECT top 25 id, question, answer from [answers] WHERE question LIKE "%" &
search & "%'
(notice the percent signs which are wildcard markers in ACCESS)

In other databases it will different wildcard markers, like asterisks ("*")
or some other character.

HTH,
PW

Reply to this Message

Bob Barrows [MVP] - 22 Jun 2008 14:00 GMT

>> SELECT top 25 id, question, answer from [answers] WHERE
>> CONTAINS(question, '""" & search & """')
>
> You need to use the LIKE keyword and put wildcard markers on both
> sides of the "search" variable.

Your answer would be correct if he was using Access. In this case, CONTAINS
is a SQL-Server-specific method used in full-text index searches.

I'm not very experienced with full-text searches, so I had to go to BOL (SQL
Books OnLine), where I found this:
************************************************************
Performing Prefix Searches
When conducting a prefix search, all entries in the column that contain text
beginning with the specified prefix will be returned. For example, to search
for all rows that contain the prefix top-, as in topple, topping, and top
itself, the query looks like this:

USE AdventureWorks;
GO
SELECT Description, ProductDescriptionID
FROM Production.ProductDescription
WHERE CONTAINS (Description, ' "top*"
' );************************************************************Note that
only prefix searches can be done. Only full words are indexed, and index
searches can only be performed if the search criterion includes the
beginning of the word.
If you need to search for strings within words, then you need to resu=ort to
PW's answer because no index can be used for searching within words.

> So in ACCESS it would be like this ...
> SELECT top 25 id, question, answer from [answers] WHERE question LIKE
> "%" & search & "%'
> (notice the percent signs which are wildcard markers in ACCESS)

No, that's wrong. These are the ODBC wildcards used by ADO recardless of
data source. In Access itself, without ADO involvement, the Jet wildcards
(similar to DOS wildcards) must be used.

> In other databases it will different wildcard markers, like asterisks
> ("*") or some other character.

This is only true if ADO is not involved. If you are running a query in the
Access Query Builder, then yes, Jet wild-cards (? and *) need to be used. If
you are running a query using ADO, then the ODBC wildcards (_ and %) need to
be used.

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Webmaster Forum / ASP / Database Access / June 2008

How do you use full text search without requiring exact terms?