Thread: How does one perform a case-insenstive query on test or char fields

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?

Thanks,
Andy

af300wsm@gmail.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';

--
erik jones <erik@myemma.com>
software development
emma(r)

On Jan 10, 2007, at 12:18 PM, Erik Jones wrote:

> af300wsm@gmail.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

I wouldn't consider this a "search", but rather a lookup.  You can find some tips on how to do this in the link below.

http://archives.postgresql.org/pgsql-php/2003-05/msg00045.php


If you need "thisfield" to be indexed, then I would suggest creating an index on the expression "LOWER(thisfield)", the below text is directly from the user guide:

For example, a common way to do case-insensitive comparisons is to use the lower function:

SELECT * FROM test1 WHERE lower(col1) = 'value';

This query can use an index, if one has been defined on the result of the lower(col1) operation:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

You can read more about expression indexes here:

http://www.postgresql.org/docs/8.2/static/indexes-expressional.html

This is already an FAQ.

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

Steve Atkins wrote:
>
> On Jan 10, 2007, at 12:18 PM, Erik Jones wrote:
>
> > af300wsm@gmail.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@postgresql.org so that your
>        message can get through to the mailing list cleanly

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

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