FULL TEXT INDEXING - FREETEXT

[kp1279]

I am very new to SQL and Access projects.

I have created the full text catalogs for my tables, so that I can search all the tables at the same time, from any of the fields in the catalogs.

I have created the following procedure:-

create procedure usp_full_text(@findtext varchar(255))
as
select set_id as URN, input_date as date_of_record from set_records where freetext (*, @findtext)

There are a few union selects under this, but

If I search for perhapse 'SMITH' from within access, then this will bring back all the records which have smith in them somewhere, which is good, however if I repeat the search for 'smit' then I get no records returned.

How can I change the code to find the parts of words, or string of charactors, and how can I set this to do a soundex search?

Hopefully all this is possible, i just need pointers on how to achieve this, or I could be sat there for days.

Many thanks for any help you can offer.

 

[vongrunt]

I guess you need full-text search equivalent of LIKE '%blah%' with some similarity checks. That's what most of people expect from FTS.

Generally speaking, there are no 100% accurate and still fast algorithms capable of doing that. Regarding FTS, FREETEXT() is less accurate and can miss especially with shorter searching terms. And CONTAINS() has some pre-requirements - term must be at the beginning of word (Smithsonian = found, Naismith = not AFAIK).




------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kp1279]

That is exactly what I want. The only other thing I can do, is to create a new temp table, of certain fields, and use a normal search of LIKE within that.

I was hopeing that the Full-Text index, was better than it seems.

If anyone has any other idea,s of how a user of the access front end, can perform a search of all fields, for a string of text that they are prompted for, please let me know.

many thanks

KP