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

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

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

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