Thread: 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

(Pg 8.4)

Well I gave it a shot and for whatever reason it doesn’t work for me.  The table got created with accountname field of type citext.  If I run a general select query the field correctly shows that it’s of type citext.  However, when I add my where clause accountname like ‘a%’, it returns no records.  If I cap it (‘A%’) then I get the expected results.  I’m using a BTree index on the field.  Don’t know if that makes a difference or not.  Has anyone gotten this citext type to work?  Is there an undocumented trick to getting it to work?  Thanks

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