pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ernest E Vogelsinger <ernest@vogelsinger.at>
To: bcschnei@attbi.com
Cc: pgsql-novice@postgresql.org
Cc: pgsql-php@postgresql.org
Subject: Re: [PHP] Case Insensitive Searching?
Date: Sun, 25 May 2003 21:51:22 +0200
Message-ID: <5.1.1.6.2.20030525214642.03109ca8@mail.vogelsinger.at> (raw)
In-Reply-To: <001301c32169$54d90fa0$5011a8c0@micronpc>

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/





view thread (4+ messages)  latest in thread

Message-ID: <5.1.1.6.2.20030525214642.03109ca8@mail.vogelsinger.at>
Permalink:  ../5.1.1.6.2.20030525214642.03109ca8@mail.vogelsinger.at/
Also on:    postgresql.org/message-id/5.1.1.6.2.20030525214642.03109ca8@mail.vogelsinger.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: ernest@vogelsinger.at, bcschnei@attbi.com, pgsql-novice@postgresql.org
  Subject: Re: [PHP] Case Insensitive Searching?
  In-Reply-To: <5.1.1.6.2.20030525214642.03109ca8@mail.vogelsinger.at>
  References: <001301c32169$54d90fa0$5011a8c0@micronpc> <5.1.1.6.2.20030525214642.03109ca8@mail.vogelsinger.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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