Skip site navigation (1) Skip section navigation (2)

Re: Mixed case text searches

From: Thom Brown <thombrown(at)gmail(dot)com>
To: Chris Campbell <ccampbell(at)cascadeds(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Mixed case text searches
Date: 2010-06-15 15:35:20
Message-ID: AANLkTil_rImE2qyHeI0gudFHZbLNsU-ABSLamL-Sgr6T@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Chris CampbellDate: 2010-06-15 18:41:25
Subject: Re: Mixed case text searches
Previous:From: Chris CampbellDate: 2010-06-15 15:25:34
Subject: Mixed case text searches

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group