Skip site navigation (1) Skip section navigation (2)

Re: Case Insensitive Searching?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: <bcschnei(at)attbi(dot)com>, <pgsql-novice(at)postgresql(dot)org>,<pgsql-php(at)postgresql(dot)org>
Subject: Re: Case Insensitive Searching?
Date: 2003-05-25 19:58:54
Message-ID: 200305251258.54689.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-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

In response to

pgsql-novice by date

Next:From: A.BhuvaneswaranDate: 2003-05-26 05:06:41
Subject: Re: psql without password
Previous:From: Ernest E VogelsingerDate: 2003-05-25 19:51:22
Subject: Re: [PHP] Case Insensitive Searching?

pgsql-php by date

Next:From: Chadwick RolfsDate: 2003-05-27 16:34:47
Subject: Re: faster output from php and postgres
Previous:From: Ernest E VogelsingerDate: 2003-05-25 19:51:22
Subject: Re: [PHP] Case Insensitive Searching?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group