From: | Ernest E Vogelsinger <ernest(at)vogelsinger(dot)at> |
---|---|
To: | <bcschnei(at)attbi(dot)com> |
Cc: | <pgsql-novice(at)postgresql(dot)org>, <pgsql-php(at)postgresql(dot)org> |
Subject: | Re: [PHP] Case Insensitive Searching? |
Date: | 2003-05-25 19:51:22 |
Message-ID: | 5.1.1.6.2.20030525214642.03109ca8@mail.vogelsinger.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice pgsql-php |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-05-25 19:58:54 | Re: Case Insensitive Searching? |
Previous Message | nolan | 2003-05-25 16:41:09 | Re: psql without password |
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-05-25 19:58:54 | Re: Case Insensitive Searching? |
Previous Message | Bruno Wolff III | 2003-05-25 13:56:30 | Re: [PHP] Case Insensitive Searching? |