Thread: Case Insensitive Searching?

Ben,

> So the question is, how do I perform a case insensitive search? Is there a
> DB setting I can set to ignore the case?

To the latter: No.

To the former, there are four ways that you can do a case-insensitive search
in PostgreSQL:

1) Using ILIKE:    SELECT * FROM sometable WHERE textfield ILIKE 'value%';
2) Using Regexp operators (see Functions and Operators in the docs):
    SELECT * FROM sometable WHERE textfield ~* 'value';
3) Using UPPER() or LOWER() to change the case of the field before comparison;
this approach can be better than 1) or 2) because these functions may be
indexed, and thus if you are doing a "begins with" or "exact match" search
your query may be indexed:
    SELECT * FROM sometable WHERE UPPER(textfield) LIKE (UPPER('value') || '%');
4) If most of your searches are "anywhere in field" searches on large text
fields, I'd reccomend a look at the two "full text search" tools available in
PostgreSQL, one in the /contrib of your source, the second from openFTS.org.

Overally, I would strongly recommend that you buy and read an introcductory
PostgreSQL book before proceeding further with your project.

--
Josh Berkus
Aglio Database Solutions
San Francisco

At 22:24 23.05.2003, Ben Schneider said:
--------------------[snip]--------------------
>I am working on a project where I am creating a PHP front end to a
>Postgres database. The schema is new but the data is old. Meaning I had to
>convert the data from the old Informix DB.
>
>Anyway all of the existing data is in all upper case. However new data
>going in will be in both upper and lower case.(As requested by the customer.)
>
>So the question is, how do I perform a case insensitive search? Is there a
>DB setting I can set to ignore the case?
>
>If not, then an example of some PHP code that can do this would be of a
>great help.
--------------------[snip]--------------------

There are a couple of ways to do that, it depends on your likes/dislikes as
well as on performance in your specific case.

You could simply use lower() or upper() in your query constraints:
    .... WHERE lower(column_name) = lower('search_value')

Use the ILIKE operator (case insensitive LIKE):
    .... WHERE column_name ILIKE '%search_value%'

However I did some timings and noticed that ILIKE is appox. 10 times slower
than LIKE. Maybe a problem on my side, but... LIKE constructs usually tend
to NOT use indexes.

To have an index at hand for a lower() search, create an index using
lower() values:
    CREATE INDEX id_lower_content ON mytable(lower(column_name))


--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/