Re: Mixed case text searches

On 15 June 2010 16:25, Chris Campbell <ccampbell(at)cascadeds(dot)com> wrote:

> 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
>
>
>
You might want citext (case-insensitive text):
http://www.postgresql.org/docs/8.4/static/citext.html

This means while the case is preserved when returning data, queries will
match insensitively. Also indexes will work as expected on them. The
alternative is to use lower() on every query and have a function index using
lower().

Regards

Thom