Keyword Searching in a Column 1

[faust13]

I'm trying to do a keyword search in a particular column. I already have the the column/tbl added to the full-text catalog, but I'm not sure what the best approach to searching it is. I've been using:

SELECT *
FROM myTbl
WHERE Search_Column LIKE '%keyword%'

It's fast and returns good results, but it misses the words that are plural/singular or different conjugations of verbs, and few other grammatical problems.

Suggestions?

Also, I'm developing a version for distribution using VB6 and Access for distribution. I'm not very familiar with what Access has to offer as far as text searching... any suggestions there?

Thanks in advance! ----------------------------------------
Is George Lucas Kidding...

 

[grahamm]

To use the full text search service you need to use the FREETEXT or CONTAINS predicates in your select stmts.
An example from SQL 7.0 BOL

SELECT *
FROM Products
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, dry) ')

matches descriptions with the words: dry, drys, drying, ...

gm

 

[faust13]

Okay, I've been trying it out. I can't seem to get it to return any records. I've tried words that I know are in the column... no luck. I tried just CONTAINS() using simple words... no luck either.

Suggestions? ----------------------------------------
Is George Lucas Kidding...

 

[grahamm]

Have you updated the full-text indexes since you added the data? Unlike database indexes, the full-text indexes are not automatically updated. Again from SQL 7.0 BOL:

"Full-text indexes are not kept up to date automatically as data is modified in the associated tables. Tables that can be updated should have their full-text indexes repopulated at appropriate intervals. The population can be time-consuming, so it is an asynchronous process that is usually run in the background. The population process has to perform sometimes complex linguistic analysis on the source strings; scanning the strings to determine word boundaries (called word-breaking) and eliminating noise-words. Both full and incremental population is supported, and is started by calling sp_fulltext_catalog. It is best to schedule jobs that run periodically to do this."

 

[faust13]

Thanks for the follow up...

The index is updated and returning records fast and well, until I use CONTAINS(), using this function returns nothing regardless of what mutation I try. ----------------------------------------
Is George Lucas Kidding...