Re: How does one perform a case-insenstive query on

This is already an FAQ.

---------------------------------------------------------------------------

Steve Atkins wrote:
>
> On Jan 10, 2007, at 12:18 PM, Erik Jones wrote:
>
> > af300wsm(at)gmail(dot)com wrote:
> >> Hello,
> >>
> >> Well, the subject line pretty much says it all. If any clarification
> >> is needed, what I want to do is as follows:
> >>
> >> SELECT * FROM table WHERE thisfield = 'some text';
> >>
> >> How would I rewrite this query to search through the table looking at
> >> the text in the column "thisfield" for the string "some text" but
> >> have
> >> it perform a case insensitive search?
> >>
> > SELECT * FROM table WHERE thisfield ilike 'some text';
>
> Or SELECT * FROM table WHERE lower(thisfield) = lower('some text');
>
> Which is less idiomatic than ilike, but has the advantage that
> you can create a functional index on thisfield to allow the
> query to use an index.
>
> They'll both only search for an exact (but case-insensitive) match,
> though - if what you're looking to do is look for any row where
> thisfield contains 'some text' somewhere within it then that's an
> entirely different thing and you'd want "... ilke '%some text%';"
>
> Cheers,
> Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +