Re: subselect question

From: Andrew McMillan <Andrew(at)catalyst(dot)net(dot)nz>
To: chriswood(at)wvda(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: subselect question
Date: 2000-12-13 03:22:38
Message-ID: 3A36EB7E.C277357@catalyst.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

chriswood(at)wvda(dot)com wrote:
>
> As in an SQL line you'd find in PHP:
>
> select * from mytable where thiscolumn = '%whatever%'
>
> Can I make thiscolumn = '%whatever%' be case-insensitive? I'm
> pretty sure I can't, but if there's a way in SQL hints would be
> appreciated. I can already do this search via a regular expression,
> but that's quite slow and I'd like a better way.

7.1 has an 'ILIKE" operator for case-insensitive comparisons.

You could try:
WHERE LOWER(thiscolumn) LIKE LOWER('%whatever%')

which should work also. Might be cleaner if you can ensure the
'whatever' is lower case already (i.e. strtolower in PHP). If you can
anchor the start of the string (get rid of the leading "%") you can add
an index on LOWER(thiscolumn) which will be used in the comparison.

Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message rob 2000-12-13 05:00:47 Re: postgres user
Previous Message Chris 2000-12-13 00:12:23 Re: Creating Log file - run in background.