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

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

On 9 Jan 2007 13:44:32 -0800, af300wsm@gmail.com <af300wsm@gmail.com> wrote:

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?

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. +