Thread: Case insensitive searchs

Case insensitive searchs

From

Andy Lewis

Date:

18 April 1999, 17:14:53

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 Antoniosan antonioSan antonioSAN ANTONIO

My question is how can I do a distinct search for all cities and only get
one of each city?

Thanks in advance.

Re: [SQL] Case insensitive searchs

From

"Gregory W Burnham"

Date:

18 April 1999, 17:18:23

I'm sure the syntax here is wrong, and I'm sure I'll be
corrected : )

But can't you do something like this:

select distict from the_table where upper(city_name) = upper("san antonio");

Gregory W Burnham
Software Engineer
Excite Labs
Faculty Of Education
Simon Fraser University
Vancouver, BC, V5A 1S6
604 291 3615 (ph)
604 291 5679 (fx)

----- Original Message -----
From: Andy Lewis <alewis@themecca.net>
To: <pgsql-sql@hub.org>
Sent: Sunday, April 18, 1999 11:14 AM
Subject: [SQL] Case insensitive searchs


> 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?
>
> Thanks in advance.
>
>
>
>
>

Re: [SQL] Case insensitive searchs

From

Andy Lewis

Date:

18 April 1999, 17:31:24

The problem is that I don't know all of the city names.

I was trying to do a:
select distinct lower(city) from mytable

This yeild a combo of all of one each of all cities that have different
case and returns them in lowercase.

Thanks

Andy


On Sun, 18 Apr 1999, Gregory W Burnham wrote:

> I'm sure the syntax here is wrong, and I'm sure I'll be
> corrected : )
> 
> But can't you do something like this:
> 
> select distict from the_table where upper(city_name) = upper("san antonio");
> 
> Gregory W Burnham
> Software Engineer
> Excite Labs
> Faculty Of Education
> Simon Fraser University
> Vancouver, BC, V5A 1S6
> 604 291 3615 (ph)
> 604 291 5679 (fx)
> 
> ----- Original Message -----
> From: Andy Lewis <alewis@themecca.net>
> To: <pgsql-sql@hub.org>
> Sent: Sunday, April 18, 1999 11:14 AM
> Subject: [SQL] Case insensitive searchs
> 
> 
> > 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?
> >
> > Thanks in advance.
> >
> >
> >
> >
> >
>

Re: [SQL] Case insensitive searchs

From

reedstrm@wallace.ece.rice.edu (Ross J. Reedstrom)

Date:

18 April 1999, 17:44:41

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

Re: [SQL] Case insensitive searchs

From

reedstrm@wallace.ece.rice.edu (Ross J. Reedstrom)

Date:

18 April 1999, 17:51:16

Hmm, what version of postgresql are you using? In my hands, the 'distinct'
works on the output of ther function call, as seen in my previous email.
You do realize that 'distinct' works on the entire returned row, not
just a single field, so, to continue my previous example:

test=> select distinct initcap(city),upper(state) from addr_table;
initcap    |upper
-----------+-----
San Antonio|TX   
(1 row)

test=> select distinct initcap(city),upper(state),oid from addr_table;
initcap    |upper|  oid
-----------+-----+-----
San Antonio|TX   |25354
San Antonio|TX   |25355
San Antonio|TX   |25356
San Antonio|TX   |25357
(4 rows)

test=> select distinct initcap(city),upper(state),oid from addr_table group by initcap(city);
initcap    |upper|  oid
-----------+-----+-----
San Antonio|TX   |25354
(1 row)

test=> 


> The problem is that I don't know all of the city names.
> 
> I was trying to do a:
> 
>     select distinct lower(city) from mytable

is that _all_ the return fields you specified?

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

Re: [SQL] Case insensitive searchs

From

Andy Lewis

Date:

18 April 1999, 17:58:34

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
>

Re: [SQL] Case insensitive searchs

From

"D'Arcy" "J.M." Cain

Date:

19 April 1999, 15:25:36

Thus spake Andy Lewis
> 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?

SELECT DISTINCT UPPER (city) FROM table;

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.