Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.9.2/8.9.1) with ESMTP id OAA82550 for ; Sun, 18 Apr 1999 14:44:40 -0400 (EDT) (envelope-from reedstrm@wallace.ece.rice.edu) Received: by wallace.ece.rice.edu via sendmail from stdin id (Debian Smail3.2.0.101) for pgsql-sql@hub.org; Sun, 18 Apr 1999 13:44:39 -0500 (CDT) Message-Id: From: reedstrm@wallace.ece.rice.edu (Ross J. Reedstrom) Subject: Re: [SQL] Case insensitive searchs In-Reply-To: from Andy Lewis at "Apr 18, 99 01:14:46 pm" To: alewis@themecca.net (Andy Lewis) Date: Sun, 18 Apr 1999 13:44:39 -0500 (CDT) Cc: pgsql-sql@hub.org X-Mailer: ELM [version 2.4ME+ PL39 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit There's more than one way to do it, and some interesting SQL text functions to use with it, as well, as demonstrated by the example below: test=> create table addr_table (city text, state text); CREATE test=> insert into addr_table values ('SAN ANTONIO', 'TX'); INSERT 25354 1 test=> insert into addr_table values ('San Antonio', 'TX'); INSERT 25355 1 test=> insert into addr_table values ('San antonio', 'tx'); INSERT 25356 1 test=> insert into addr_table values ('san antonio', 'Tx'); INSERT 25357 1 test=> select * from addr_table; city |state -----------+----- SAN ANTONIO|TX San Antonio|TX San antonio|tx san antonio|Tx (4 rows) test=> select * from addr_table group by city; city |state -----------+----- SAN ANTONIO|TX San Antonio|TX San antonio|tx san antonio|Tx (4 rows) test=> select * from addr_table group by lower(city); city |state -----------+----- SAN ANTONIO|TX (1 row) test=> select initcap(city),upper(state) from addr_table group by lower(city); initcap |upper -----------+----- San Antonio|TX (1 row) test=> select distinct initcap(city),upper(state) from addr_table; initcap |upper -----------+----- San Antonio|TX (1 row) > I have a table that has city names in it. Some of the cities have a number > of different cases. For instance I may have: > > San Antonio > san antonio > San antonio > SAN ANTONIO > > My question is how can I do a distinct search for all cities and only get > one of each city? HTH, Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005