Mixed case text searches

From: Chris Campbell <ccampbell(at)cascadeds(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Mixed case text searches
Date: 2010-06-15 15:25:34
Message-ID: 453A24085F801842AEA8D0B6B269065D2F8BA64CE3@HDMC.cds.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Thom Brown 2010-06-15 15:35:20 Re: Mixed case text searches
Previous Message Jean-Yves F. Barbier 2010-06-15 13:57:15 Re: (not so?) silly question