Re: Mixed case text searches

Hi list people.  Okay I’ve read the documentation.  Now it’s time to talk to people that actually do this for a living.  Mixed case searches, what is the best practice?

I’m searching for an account name:  Acme Rockets Inc.

 

strSearchString = ‘acme%’

Select * From Accounts Where AccountName = strSearchString

This will of course fail because the case doesn’t match.  So what is the best practice for performance?

 

I could use the Lower() function:

strSearchString = lower(‘acme%’)

Select * From Accounts Where lower(AccountName) = strSearchString

 

Or I could use the ilike operator

strSearchString = ‘acme%

Select * From Accounts Where AccountName ilike  strSearchString

 

It’s also been suggested that I keep a companion column that mirrors the account name column which is forced to lower case.  This seems, well a bit desperate to me.

 

So, from a performance standpoint, what are people doing and why?

 

Many thanks for your replies.

 

Chris Campbell