pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCase insensitive searchs
7+ messages / 4 participants
[nested] [flat]
* Case insensitive searchs
@ 1999-04-18 18:14 Andy Lewis <alewis@themecca.net>
0 siblings, 3 replies; 7+ messages in thread
From: Andy Lewis @ 1999-04-18 18:14 UTC (permalink / raw)
To: pgsql-sql@hub.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Case insensitive searchs
@ 1999-04-18 18:16 Gregory W Burnham <gburnham@sfu.ca>
parent: Andy Lewis <alewis@themecca.net>
2 siblings, 1 reply; 7+ messages in thread
From: Gregory W Burnham @ 1999-04-18 18:16 UTC (permalink / raw)
To: Andy Lewis <alewis@themecca.net>; pgsql-sql@hub.org
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.
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Case insensitive searchs
@ 1999-04-18 18:31 Andy Lewis <alewis@themecca.net>
parent: Gregory W Burnham <gburnham@sfu.ca>
0 siblings, 1 reply; 7+ messages in thread
From: Andy Lewis @ 1999-04-18 18:31 UTC (permalink / raw)
To: Gregory W Burnham <gburnham@sfu.ca>; +Cc: pgsql-sql@hub.org
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.
> >
> >
> >
> >
> >
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Case insensitive searchs
@ 1999-04-18 18:44 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: Andy Lewis <alewis@themecca.net>
2 siblings, 1 reply; 7+ messages in thread
From: reedstrm@wallace.ece.rice.edu @ 1999-04-18 18:44 UTC (permalink / raw)
To: alewis@themecca.net; +Cc: pgsql-sql@hub.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Case insensitive searchs
@ 1999-04-18 18:51 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: Andy Lewis <alewis@themecca.net>
0 siblings, 0 replies; 7+ messages in thread
From: reedstrm@wallace.ece.rice.edu @ 1999-04-18 18:51 UTC (permalink / raw)
To: alewis@themecca.net; +Cc: pgsql-sql@hub.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Case insensitive searchs
@ 1999-04-18 18:58 Andy Lewis <alewis@themecca.net>
parent: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Andy Lewis @ 1999-04-18 18:58 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: pgsql-sql@hub.org
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Case insensitive searchs
@ 1999-04-19 16:25 D'Arcy J.M. Cain <darcy@druid.net>
parent: Andy Lewis <alewis@themecca.net>
2 siblings, 0 replies; 7+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-04-19 16:25 UTC (permalink / raw)
To: alewis@themecca.net; +Cc: pgsql-sql@hub.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-04-19 16:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-18 18:14 Case insensitive searchs Andy Lewis <alewis@themecca.net>
1999-04-18 18:16 ` Gregory W Burnham <gburnham@sfu.ca>
1999-04-18 18:31 ` Andy Lewis <alewis@themecca.net>
1999-04-18 18:51 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-04-18 18:44 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-04-18 18:58 ` Andy Lewis <alewis@themecca.net>
1999-04-19 16:25 ` D'Arcy J.M. Cain <darcy@druid.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox