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