Thread: subselect question

subselect question

From
chriswood@wvda.com
Date:
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.

--------------------------------------------------------
Christopher Wood, Community Access Technical Coordinator
Western Valley Development Authority
Nova Scotia's Smart Community
Box 251 Bridgetown, Nova Scotia  B0S 1C0
tel: (902) 665-4083/ fax: (902) 665-2878
www.wvda.com www.wvcn.ns.ca
--------------------------------------------------------

Re: subselect question

From
Andrew McMillan
Date:
chriswood@wvda.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@catalyst.net.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