Re: [PHP] Case Insensitive Searching?

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.

Thanks,

Ben

On Fri, May 23, 2003 at 13:24:36 -0700,
Ben Schneider <bcschnei(at)attbi(dot)com> wrote:
>
> 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 you were using "like", you can use "ilike" instead. There is also
a case insensitive version of the regular expression pattern matching
operator. In some cases you may want to use the "lower" function
to get the data in just one case before doing a comparison. Without
seeing how you are doing your searches now it is hard to provide
specifics on what to do to make them case insensitive.

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/

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