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

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


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

Roy,

did you see the correction of my first posting?

One statement uses the TextIndex (contains...) the other not (like...)

Uli

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

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

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]

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

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

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