![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
![]() |
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 ![]() | 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 ![]() | 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 Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |