On 9 Jan 2007 13:44:32 -0800, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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
