pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: bcschnei@attbi.com
To: pgsql-novice@postgresql.org
To: pgsql-php@postgresql.org
Subject: Re: Case Insensitive Searching?
Date: Sun, 25 May 2003 12:58:54 -0700
Message-ID: <200305251258.54689.josh@agliodbs.com> (raw)
In-Reply-To: <001301c32169$54d90fa0$5011a8c0@micronpc>
References: <001301c32169$54d90fa0$5011a8c0@micronpc>

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



view thread (4+ messages)

Message-ID: <200305251258.54689.josh@agliodbs.com>
Permalink:  ../200305251258.54689.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200305251258.54689.josh@agliodbs.com

 · 

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: josh@agliodbs.com, bcschnei@attbi.com, pgsql-novice@postgresql.org
  Subject: Re: Case Insensitive Searching?
  In-Reply-To: <200305251258.54689.josh@agliodbs.com>
  References: <001301c32169$54d90fa0$5011a8c0@micronpc> <200305251258.54689.josh@agliodbs.com>

* 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