Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Weird search results with SELECT LIKE in a large table |
Sun, Aug 23 2009 10:41 AM | Permanent Link |
Mats Berggren | 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 |
Mon, Aug 24 2009 12:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Aug 24 2009 6:32 PM | Permanent Link |
Mats Berggren | 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 |
Tue, Aug 25 2009 6:55 AM | Permanent Link |
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 |
Tue, Aug 25 2009 8:04 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |