Using DBISAM 4.29 Build 1

I'm trying to use TEXTSEARCH on a memo field to search for words contained within the memo field.  Each memo field contains paragraphs of text.

SELECT
 PreorderNo, Ordertext
FROM
 PreorderOrderText
WHERE
 TEXTSEARCH('HERMAN' in OrderText)

This returns nothing, I'm assuming because 'HERMAN' is in the middle of the text.  I've tried adding a trailing '*' and same result.

If I use LIKE '%HERMAN%' in my where clause, I get 47,000+ records as a result.

If I can't search for words within the memo field, then what good is TEXTSEARCH?

John

John

> TEXTSEARCH('HERMAN' in OrderText)
>
>This returns nothing, I'm assuming because 'HERMAN' is in the middle of the text. I've tried adding a trailing '*' and same result.
>
>If I use LIKE '%HERMAN%' in my where clause, I get 47,000+ records as a result.

I switched to ElevateDB back at 4.25 so things may have changed. The only way I can see that you may get nothing with TEXTSEARCH and 47k records with like is if HERMAN does not occur as a word, or if you have a custom word generator filtering it out. Adding a * will return results like

HERMANING, HERMANS etc but not AHERMAN

Can you post an example of a paragraph containing HERMAN.

Roy Lambert [Team Elevate]

John,

<< If I use LIKE '%HERMAN%' in my where clause, I get 47,000+ records as a
result.

If I can't search for words within the memo field, then what good is
TEXTSEARCH? >>

The operative word here is "words".  If HERMAN is in the middle of another
word, then it isn't a "word", per se.  The text search functionality indexes
words only, and can only search for words that match fully, or partially
with a trailing wildcard, from left-to-right.

--
Tim Young
Elevate Software
www.elevatesoft.com

Here's some sample text pulled from one of my 47,000 records...

- - - - - - - - - -

STANDARD) FURNITURE

PRODUCT TYPE:  HERMAN MILLER & MERIDIAN

FOR:  CPS BEAUMONT BUILDING

SCOPE OF WORK:

1)  DELIVER AND INSTALL (100) HMI AO3 WORKSTATIONS
2)  DELIVER AND INSTALL (16) MERIDIAN PASSAGE OFFICES & (2) PASSAGE RECEPTION UNITS
3)  DELIVER AND INSTALL (1) MERIDIAN 500O SERIES UNIT FOR SECURITY OFFICER
4)  DELIVER ALL SEATING FOR ALL WORKSTATIONS AND OFFICES
5)  DELIVER AND INSTALL (130) HUMANSCALE KEYBOARD TRAYS
6)  DELIVER HMI SEATING FOR INTERVIEW ROOMS, CONFERENCE ROOMS, LUNCH ROOM ETC.
7)  DELIVER (2) BOOKCASES FOR LOBBY

John,

<< Here's some sample text pulled from one of my 47,000 records... >>

Did you verify the table to ensure that there isn't any problem with the
indexes ?  If the table comes back clean, please send me a copy of the table
via email so that I can take a look.

Also, did you customize any of the text filtering/indexing through the
TDBISAMEngine component ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Yes, I verified.  Where should I send the table?

John

Also, did you customize any of the text filtering/indexing through the
TDBISAMEngine component ?


No..

John

John


That should certainly looks like it should be indexed. Offhand I can't remember the defaults but I do know that for part of an app I swop #32 (soft space) for #160 (hard space) visually the same but, in my app at least, handled differently. If something like that's been done in your app HERMAN MILLER could be one word.

Roy Lambert [Team Elevate]

John,

Per email:

<< Here's the table as I described in the newsgroups. Again, using the query
below will yield 48,064 records. >>

Your problem is your text index.  If you open the table using the DBSYS
utility and then click on the Full Text Indexing tab, you'll see that the
full text index has only a single include character - #1.

To fix this, close the table and do the following in DBSYS:

1) Select Utilities/Alter Table.
2) Click on the Full Text Indexing tab.
3) Click on the Default button.
4) Click on the Alter button.

That should fix everything.

Tim Young
Elevate Software
www.elevatesoft.com

Works, thanks Tim!