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