Обсуждение: 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
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. +