Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Case insensitive indexes |
Mon, Jun 9 2014 4:36 AM | Permanent Link |
Matthew Jones | I have a column containing email addresses. I want to ensure that it is
treated case-independently, however the user enters it. I created a case insensitive index on the column. The query includes: WHERE (LOWER(AccountEmail) = :EMAIL) But this doesn't use the index. If I don't include the LOWER then the index is used. But is it doing what I want, or is it just hiding a problem? How can I tell? The plan created shows that it is using the index. Do I just trust it? Or is it going to use the index then check the values actually match? How should I be checking for values case-independently? Thanks, -- Matthew Jones |
Mon, Jun 9 2014 6:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
You have been found guilty of DBISAMthink! <sound of mad cackling> Its all changed. NOw you need to specify collations. 1. make sure the field is set to case insensitivity if you use ansi this will give a collation of ANSI_CI 2. make sure the index is defined as case insensitive -as well that's it. Then WHERE (AccountEmail = :EMAIL) is case insensitive ElevateDB will only match / use indices if the collation matches. If you have the column defined as case sensitive and the index as case sensitive then you have to specify the collation WHERE (AccountEmail COLLATE ANSI_CI = :EMAIL) Roy Lambert |
Mon, Jun 9 2014 6:54 AM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> You have been found guilty of DBISAMthink! > > <sound of mad cackling> Guilty, but trying to change! 8-) > Its all changed. NOw you need to specify collations. > > 1. make sure the field is set to case insensitivity if you use ansi > this will give a collation of ANSI_CI 2. make sure the index is > defined as case insensitive -as well Ahah, I appear to have specified case insensitive at some point. That explains why it is working. Thank you for the detailed explanation - I understand it now (I hope). -- Matthew Jones |
Mon, Jun 9 2014 9:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
>Guilty, but trying to change! 8-) I was found guilty lots of times. Its only the cost of the air fare that stopped Tim coming over and dragging me back to jail! Roy Lambert ps only joking |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |