We are trying to optimize order search functionality in our product. Currently it has
bunch of LIKE conditions with the wild card at the beginning and at the end of the LIKE

SELECT *
from package
where
(Upper(PackageTrackingNumber) LIKE '%ALEX%') or
(Upper(HistCompany) LIKE '%ALEX%') or
(Upper(HistName) LIKE '%ALEX%') or
(Upper(PackageReference1) LIKE '%ALEX%')


We decided to try Full Text Search feature of DBISAM to improve performance of the query
above. We added a TEXT INDEX and modified query to use TEXTSEARCH() command

alter table package
TEXT INDEX (HistName, HistCompany, PackageReference1, PackageTrackingNumber)

SELECT *
from package
where
TEXTSEARCH( 'alex' in HistName ) or
TEXTSEARCH( 'alex' in HistCompany ) or
TEXTSEARCH( 'alex' in PackageTrackingNumber ) or
TEXTSEARCH( 'alex' in PackageReference1 )


Here is a difference between results that we found

First query (LIKE) will return all records with customer name ALEX, ALEXA, ALEXANDER
Second query (TEXTSEARCH) only returns records with customer name ALEX

Is there any way to make TEXTSEARCH to perform exactly the same way as LIKE with wildcards
and look for partial matches as well?

Alex

AlexU


TEXTSEARCH searches for words not strings. You can get partial wildcard matching by putting a * at the end of a word, but not at the beginning.

With your "ALEX" example it would work doing

SELECT *
from package
where
TEXTSEARCH( 'alex*' in HistName ) or
TEXTSEARCH( 'alex*' in HistCompany ) or
TEXTSEARCH( 'alex*' in PackageTrackingNumber ) or
TEXTSEARCH( 'alex*' in PackageReference1 )


..
Roy Lambert

Alex,

<< Is there any way to make TEXTSEARCH to perform exactly the same way as
LIKE with wildcards and look for partial matches as well? >>

Not currently, no.  It is something that we are investigating for ElevateDB
at some point, and then it might be back-ported into DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com