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

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 (view raw, whole thread or download thread mbox)
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


pgsql-novice by date

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

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