Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Using Full Text Search to find partial matches |
Fri, Nov 17 2006 7:02 PM | Permanent Link |
AlexU | 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 |
Sat, Nov 18 2006 6:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Nov 20 2006 4:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |