Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 15 total |
Different results using TextIndex |
Wed, Aug 28 2013 1:39 PM | Permanent Link |
Uli Becker | Not sure if I'm blind, but I have problems with quite a simple query:
I am looking for all records which contain both "steuer" and "ident" in one of the fields "Caption", "Search" or "Text". Only the field "Text" has a TextIndex. I expect 2 records in the result: 1st record contains "steuer" and "ident" both in "Caption" 2nd record contains "steuer" and "ident" both in "Text" The statement using the TextIndex returns only 1 record ("steuer" and "ident" both in "Text"): SELECT * from Folders where (BenutzerID = 1) AND (Caption like '%steuer%' OR Search like '%steuer%' OR text like '%steuer%') AND (Caption like '%ident%' OR Search like '%ident%' OR text like '%ident%') while this statement returns 2 records correctly: SELECT * from Folders WHERE (BenutzerID = 1) AND (Caption like '%steuer%' OR Search like '%steuer%' OR text like '%steuer%') AND (Caption like '%ident%' OR Search like '%ident%' OR text like '%ident%') Any idea what I'm doing wrong? Thanks Uli |
Wed, Aug 28 2013 1:43 PM | Permanent Link |
Uli Becker | Sorry, I copied the same statement twice, here the correction:
The statement using the TextIndex returns only 1 record ("steuer" and "ident" both in "Text"): SELECT* from Folders where (BenutzerID = 1) AND (Caption like '%steuer%' OR Search like '%steuer%' OR text contains '*steuer*') AND (Caption like '%ident%' OR Search like '%ident%' OR text contains '*ident*') while this statement returns 2 records correctly: SELECT * from Folders WHERE (BenutzerID = 1) AND (Caption like '%steuer%' OR Search like '%steuer%' OR text like '%steuer%') AND (Caption like '%ident%' OR Search like '%ident%' OR text like '%ident%') |
Wed, Aug 28 2013 2:03 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
Whatever is affecting you is also making my eyes go funny. Apart from the fact that you have where vs WHERE I can find no difference between the two sattements. I copies into notepad, turned off wordwrap to see and they are identical. Roy Lambert |
Wed, Aug 28 2013 3:20 PM | Permanent Link |
Uli Becker | Roy,
did you see the correction of my first posting? One statement uses the TextIndex (contains...) the other not (like...) Uli |
Wed, Aug 28 2013 5:31 PM | Permanent Link |
Michael Riley ZilchWorks | Uli Becker wrote:
> Sorry, I copied the same statement twice, here the correction: > > The statement using the TextIndex returns only 1 record ("steuer" and > "ident" both in "Text"): > > SELECT* from Folders where (BenutzerID = 1) > AND (Caption like '%steuer%' OR Search like '%steuer%' OR text > contains '*steuer*') AND (Caption like '%ident%' OR Search like > '%ident%' OR text contains '*ident*') > > while this statement returns 2 records correctly: > > SELECT * from Folders WHERE (BenutzerID = 1) > AND (Caption like '%steuer%' OR Search like '%steuer%' OR text like > '%steuer%') AND (Caption like '%ident%' OR Search like '%ident%' OR > text like '%ident%') Uli, The documentation for CONTAINS used in your first example states that an * can be used to specify a trailing wildcard. You have a leading asterisk. Try removing the leading asterisk and see what happens. For example: SELECT * from Folders where (BenutzerID = 1) AND ( Caption like '%steuer%' OR Search like '%steuer%' OR text contains 'steuer*' ) AND ( Caption like '%ident%' OR Search like '%ident%' OR text contains 'ident*' ) -- Michael Riley GySgt USMC (Ret) www.zilchworks.com |
Thu, Aug 29 2013 2:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Michael
Wildcards at front and start is an enhancement that from your comments hasn't made it to the manual yet. I know because I've had to enhance my TextSearch external function to do the same. Roy Lambert |
Thu, Aug 29 2013 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
>did you see the correction of my first posting? Not at the time I posted. >One statement uses the TextIndex (contains...) the other not (like...) Looking at that my first guess would be case sensitivity. CONTAINS is case insensitive (I think but I do get confused with it), LIKE depends on the collation. SO what collation do you have for the columns in the query? Roy Lambert [Team Elevate] |
Thu, Aug 29 2013 4:03 AM | Permanent Link |
Uli Becker | Roy,
> Looking at that my first guess would be case sensitivity. CONTAINS is case insensitive (I think but I do get confused with it), LIKE depends on the collation. SO what collation do you have for the columns in the query? All Fields and Indexes DE_CI Uli |
Thu, Aug 29 2013 4:05 AM | Permanent Link |
Uli Becker | Michael,
> Try removing the leading asterisk and see what happens. That doesn't change anything. But - interesting - select * from Folders where (BenutzerID = 1) AND (Caption like '%steuer%' or Search like '%steuer%' or text contains 'steuer') AND (Caption like '%ident%' or Search like '%ident%' or text contains 'ident') Removing both * from contains results in 0 records. Though there is 1 record with both "steuer" and "ident" in the field "caption". Uli |
Thu, Aug 29 2013 4:25 AM | Permanent Link |
Uli Becker | In addition:
Removing all * from contains results in 0 records. Though there is 1 record with both "steuer" and "ident" in the field "caption". Uli |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |