Re: subselect question

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