Full Text Searching

[robinson_pm]

hi all,

How can i enable full text searching (indexing)-- If you might recall ... when windows is newly installed .. and for the first time you go for windows help ... it gives a message Preparing Index for first use.

This type of search is far more faster and accurate than the usual

select * from tablename where fieldname like 'abc%'

Is there any windows component that can be used or any third party product available ?

thanks
Robinson

[mebhas]

are you using db or in text files?

in db, you can use the indexing function of access. in text files, you can use the instr function of vb.

[robinson_pm]

-------------------------------
in db, you can use the indexing function of access ....
----------------------------------------------
Are you talking about adding index to a column ?
If so my requirement is different ...

suppose some one searches "candle in the wind" -- what full text indexing does is -- it searches for records containing "candle" .
ignoring "in" and "the" it searches for records containing word "wind" then it sorts for recordset containing both words and displays the results in order fist where both keywords are available and then with one keyword... its a pretty complex work and this facility is available in MSSQL7 Provided the O/S is NT/2000 server version (not professional/ standard).

What full text does it it searches the entire database (columns specified) for such unique words and stores it as a table each word accociated with a key (Pre indexed searching)....
So that when some one searches... it need not actually search the entire content ... but search in that specific table... which is many times more faster than the usual search...


To build such a thing .. its going to consume a lot of time ....
thats why i posted for help ...if somebody knew about any such available component

By the way I will be using Access on win 98... so i cannot use the feature available on SQL7 on NT/2000 server version.

thanks
Robinson

[si_the_geek]

as far as I know there isn't a built in function, but it wouldn't be particularly hard to write.

First create the table (with columns for: source table, source field, key, and the word).

to fill it loop thru each table/column you want, split the field into words (ignoring words you think are common, such as "the"), and write a row in the "search" table for every word.

(nb: i would actually recommend having a count as well, as the same word could be in a field more than once)


then to search you can simply query the "search" table for any words in the users input, and use the reference values to map to the original data.

[robinson_pm]

hi si_the_geek,

Its not about the function being hard to write ... its the time cunsumed ... with my kind of knowledge in VB (somewhere between beginer to intermediate) -- it will take me 20 days atleast...I have used this Full text indexing feature of MSSQL in ASP... but without SQL i feel helpless.

Question is why reinvent the wheel ... when you already have a far more better one available...

I am quite sure this particular DLL/ component exist in all the OS version of windows...because the message ... preparing index for first use ... i have seen right from windows 95 to windows 2000

Only if i could find what dll windows calls when that message comes..

[si_the_geek]

Originally posted by robinson_pm
I am quite sure this particular DLL/ component exist in all the OS version of windows...because the message ... preparing index for first use ... i have seen right from windows 95 to windows 2000

Only if i could find what dll windows calls when that message comes..

no, that is the Windows HELP system - it is nothing to do with databases

[robinson_pm]

hi si_the_geek,

cool ... can i use this Windows HELP system ---in any way for my database..because i feel the way it search its great... and i really want my application to have that kind of search.

Robinson

[si_the_geek]

Originally posted by robinson_pm
hi si_the_geek,

cool ... can i use this Windows HELP system ---in any way for my database..because i feel the way it search its great... and i really want my application to have that kind of search.

Robinson

no! the help system is not related to databases in ANY way. in any case, it would be far easier to re-write the entire thing a hundred times over than it would be try to use the help system in that manner (which believe me would not be easy, if it is possible at all)

the best you can do is what I have already suggested.

[robinson_pm]

well then i guess i have to write something of my own ....
and if this search turn out to be good then i will be more than happy to post it to this group ... so that some one else need not go thru rewriting this again.

thanks
Robinson

[Wokawidget]

What I do is have a text box, say txtDescription then I use the following code:

If InStr(1,txtDescription,.Text,"*") > 0 OR InStr(1,txtDescription.Text,"_") > 0 Then
   strSQL = strsSQL & "AND tblMyTable.Description LIKE '" & Replace(txtDescription, "*", "%") & "' "
ELSE
   strSQL = strsSQL & "AND tblMyTable.Description = '" & txtDescription.Text & "' "
End If

This allows the user to add a * for a wild card, and the _ just means one letter...

So searching for "Wo_f" Would return:

Woof
Wogf
Woef
Woff

etc

So searching for "Woof%" Would return:

Woof
Woofjhfsd
Woofbadger
WoofFish

You get the picture...

Help file search is completely different, don't get them confused with each other....it's like saying a peddle bike can go 20mph, and a car can go 100mph...since then both move at a decent rate of mph's then they must be the same...

Woka

PS

[robinson_pm]

hi Wokawidget ,

the code you posted will actually search for a word... actully i wanted to search for a phrase ... not necessary the exact phrase is found in the DB ...

Although an alternative to do this will be split the entire phrase and append "%" at the end of every word.. but that would be very slow...

eg : some one searches for "candle in the wind" ---
sql ="select * from ....where column_name like 'candle%' or 'wind%'"
-- ignoring "in" and "the"



thanks
Robin