Hi,

I use ElevateDB VCL-STD-SRC (Unicode) version 2.02 Build 15 with Delphi 2006.
I also use ElevateDB-ADD Version 2.02 Build 15.

I get weird results when I search a text field in one of my database tables.
The table "Perstab" is quite large (750655 records). Each record contains information about one person.
The field "fornamn" (varchar (80) COLLATE SVE_CI) contains given names for persons.

In my application I use the function TEDBTable.FindNearest to search the field "fornamn".
I get identical results when I use a SQL SELECT LIKE statement from the EDB Manager.

For some search values everything works fine:

Example 1:
select fornamn from Perstab where fornamn like 'rob%'

Result: 411 hits
Various names starting with 'rob'


Example 2:
select fornamn from Perstab where fornamn like 'robert%'

Result: 319 hits
Various names starting with 'robert'


But when I use other search values I get weird results:


Example 3:
select fornamn from Perstab where fornamn like 'v%'

Result: 3 hits
V.
V.
V(    )?


Example 4:
select fornamn from Perstab where fornamn like 've%'

Result: No records found


Example 5:
select fornamn from Perstab where fornamn like 'ver%'

Result: 17 hits
Vera (17 times)


Example 6:
select fornamn from Perstab where fornamn like 'vern%'

Result: 3 hits
Vern (3 times)


Example 7:
select fornamn from Perstab where fornamn like 'verner%'

Result: 33 hits
Verner (33 times)


These results are inexplicable. What is wrong here?
I have tried to rebuild the table. Same result.
I have also tried to drop the index and create a new index. Same result.
I have also tried to export the field to a text file and then import the text file to a new table with a new index. Same result.

I have never come across this problem with small tables. Can it have something to do with tablesize or indexsize?
It looks as if it might be a problem with the index itself. I get the same result with SQL as with TEDBTable.

I would be very grateful if someone could help me with this problem.

Regards,
Mats Berggren

Mats,

<< These results are inexplicable. What is wrong here?
I have tried to rebuild the table. Same result.
I have also tried to drop the index and create a new index. Same result.
I have also tried to export the field to a text file and then import the
text file to a new table with a new index. Same result. >>

Can you email me the table files and database catalog (zipped, please) ?
I'll have to check it out here before I can comment further.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thank you for your quick response.

I have sent an email to support@elevatesoft.com

I hope you can solve my problem.

Mats Berggren

Hi Tim,

One more thing about my indexing problem.

It seems to have something to do with the letters V and W.
It looks as if the search fails when the search value contains V or W.

Could it have something to to with the Swedish COLLATE? (COLLATE SVE_CI).
One peculiar thing with Swedish sort order is that the letters V and W are searched and sorted together as if they were the same letter.
In English V and W are usually treated as two different letters.

Regards,
Mats Berggren

Mats,

<< It seems to have something to do with the letters V and W.
It looks as if the search fails when the search value contains V or W.

Could it have something to to with the Swedish COLLATE? (COLLATE SVE_CI).
One peculiar thing with Swedish sort order is that the letters V and W are
searched and sorted together as if they were the same letter.  In English V
and W are usually treated as two different letters. >>

It most certainly has something to do with that.  We've seen similar issues
with 'ss' and LIKE with German collations, and a fix will be in the next
build/release for this.  However, I need to still run some tests on your
data (thanks for the data first before commenting on whether there are
any other issues involved.

--
Tim Young
Elevate Software
www.elevatesoft.com