pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedCase Insensitive Searching?
4+ messages / 4 participants
[nested] [flat]
* Case Insensitive Searching?
@ 2003-05-23 20:24 Ben Schneider <bcschnei@attbi.com>
0 siblings, 3 replies; 4+ messages in thread
From: Ben Schneider @ 2003-05-23 20:24 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-php
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PHP] Case Insensitive Searching?
@ 2003-05-25 13:56 Bruno Wolff III <bruno@wolff.to>
parent: Ben Schneider <bcschnei@attbi.com>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2003-05-25 13:56 UTC (permalink / raw)
To: Ben Schneider <bcschnei@attbi.com>; +Cc: pgsql-novice@postgresql.org; pgsql-php
On Fri, May 23, 2003 at 13:24:36 -0700,
Ben Schneider <bcschnei@attbi.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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PHP] Case Insensitive Searching?
@ 2003-05-25 19:51 Ernest E Vogelsinger <ernest@vogelsinger.at>
parent: Ben Schneider <bcschnei@attbi.com>
2 siblings, 0 replies; 4+ messages in thread
From: Ernest E Vogelsinger @ 2003-05-25 19:51 UTC (permalink / raw)
To: bcschnei@attbi.com; +Cc: pgsql-novice@postgresql.org; 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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Case Insensitive Searching?
@ 2003-05-25 19:58 Josh Berkus <josh@agliodbs.com>
parent: Ben Schneider <bcschnei@attbi.com>
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2003-05-25 19:58 UTC (permalink / raw)
To: bcschnei@attbi.com; pgsql-novice@postgresql.org; pgsql-php
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-25 19:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-23 20:24 Case Insensitive Searching? Ben Schneider <bcschnei@attbi.com>
2003-05-25 13:56 ` Bruno Wolff III <bruno@wolff.to>
2003-05-25 19:51 ` Ernest E Vogelsinger <ernest@vogelsinger.at>
2003-05-25 19:58 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox