Обсуждение: 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 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.
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. > > > > >
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. > > > > > > > > > > >
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
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
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 >
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.