Re: [SQL] Case insensitive searchs

Thanks much! 

I've solved my problem.

Much appreciated.

Andy

On Sun, 18 Apr 1999, Ross J. Reedstrom wrote:

> 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., <reedstrm@rice.edu> 
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St.,  Houston, TX 77005
>