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