pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedCase sensitivity when searching for and displaying data
6+ messages / 5 participants
[nested] [flat]
* Case sensitivity when searching for and displaying data
@ 2003-09-03 22:31 Lynna Landstreet <lynna@gallery44.org>
0 siblings, 4 replies; 6+ messages in thread
From: Lynna Landstreet @ 2003-09-03 22:31 UTC (permalink / raw)
To: pgsql-php
Hello,
I've gotten a simple PHP search page working on the artists database that
I've been developing these past few months, but I'm running into a few
problems with PostgreSQL's case sensitivity.
Ideally I'd like the search text to case insensitive, so that if the user
enters "jane smith", "Jane Smith" or "JANE SMITH", they'll still find the
record for Jane Smith. But I'm not sure how to do that.
I know that in PHP you can use strtoupper() and strtolower() to change the
case of text, but any solution I can think of with forcibly changing the
case of the search text the user inputs would be problematic given that I
don't necessarily know what case the names are in in the actual database -
most of them would be in standard title case, with the first character
capitalized and the rest lower case, but these are artists we're talking
about, so some of them insist on having their name in all lower case and
that sort of thing. Plus some artists from other cultures have
capitalization in their name that doesn't follow the English standard (de
Broin, di Maria, etc.). So I think I need a way of making the actual SQL
query case-insensitive, and I'm not sure how or even if that can be done.
Secondly, the case sensitivity thing is also messing up the order of the
displayed results. Right now I have them set to order by lastname, and that
makes any names that begin with a lower case character come at the end of
the list because of the order of the ASCII values of the letters, rather
than where they would normally be in alphabetical order. Does anyone know if
there's a way around this?
Thanks,
Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Case sensitivity when searching for and displaying data
@ 2003-09-03 23:18 Rod Taylor <rbt@rbt.ca>
parent: Lynna Landstreet <lynna@gallery44.org>
3 siblings, 0 replies; 6+ messages in thread
From: Rod Taylor @ 2003-09-03 23:18 UTC (permalink / raw)
To: Lynna Landstreet <lynna@gallery44.org>; +Cc: pgsql-php
On Wed, 2003-09-03 at 18:31, Lynna Landstreet wrote:
> Hello,
>
> I've gotten a simple PHP search page working on the artists database that
> I've been developing these past few months, but I'm running into a few
> problems with PostgreSQL's case sensitivity.
>
> Ideally I'd like the search text to case insensitive, so that if the user
> enters "jane smith", "Jane Smith" or "JANE SMITH", they'll still find the
> record for Jane Smith. But I'm not sure how to do that.
SELECT * FROM table WHERE upper(column) = upper('search value');
> Secondly, the case sensitivity thing is also messing up the order of the
SELECT * FROM table ORDER BY upper(column);
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Case sensitivity when searching for and displaying data
@ 2003-09-03 23:20 Robby Russell <rrussell@commandprompt.com>
parent: Lynna Landstreet <lynna@gallery44.org>
3 siblings, 0 replies; 6+ messages in thread
From: Robby Russell @ 2003-09-03 23:20 UTC (permalink / raw)
To: Lynna Landstreet <lynna@gallery44.org>; pgsql-php
Lynna Landstreet wrote:
> Hello,
>
> I've gotten a simple PHP search page working on the artists database that
> I've been developing these past few months, but I'm running into a few
> problems with PostgreSQL's case sensitivity.
>
PostgreSQL has regular expression capability.
Try this with case-INsensitivity
SELECT * FROM table WHERE name ~* 'nAmE';
If you want it without insensitive...remove the asterisk.
-Robby
--
Robby Russell, | Sr. Administrator / Lead Programmer
Command Prompt, Inc. | http://www.commandprompt.com
rrussell@commandprompt.com | Telephone: (503) 222.2783
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Case sensitivity when searching for and displaying data
@ 2003-09-04 02:40 Luis H <pgsql-php@geekhouse.no-ip.com>
parent: Lynna Landstreet <lynna@gallery44.org>
3 siblings, 0 replies; 6+ messages in thread
From: Luis H @ 2003-09-04 02:40 UTC (permalink / raw)
To: pgsql-php
----- Original Message -----
From: "Lynna Landstreet" <lynna@gallery44.org>
To: <pgsql-php@postgresql.org>
Sent: Wednesday, September 03, 2003 6:31 PM
Subject: [PHP] Case sensitivity when searching for and displaying data
> Hello,
>
> I've gotten a simple PHP search page working on the artists database that
----- Original Message -----
From: "Lynna Landstreet" <lynna@gallery44.org>
To: <pgsql-php@postgresql.org>
Sent: Wednesday, September 03, 2003 6:31 PM
Subject: [PHP] Case sensitivity when searching for and displaying data
> Ideally I'd like the search text to case insensitive, so that if the user
> enters "jane smith", "Jane Smith" or "JANE SMITH", they'll still find the
> record for Jane Smith. But I'm not sure how to do that.
For the searching, I'd read through the pattern matching section of pgsql
doc:
http://www.postgresql.org/docs/7.3/static/functions-matching.html
For simple searches, ILIKE performs a case-insensitive search, and for
Regular Expressions, use ~* instead of ~.
> Secondly, the case sensitivity thing is also messing up the order of the
> displayed results. Right now I have them set to order by lastname, and
that
> makes any names that begin with a lower case character come at the end of
> the list because of the order of the ASCII values of the letters, rather
> than where they would normally be in alphabetical order. Does anyone know
if
> there's a way around this?
In terms of sorting, you could use the function lower(string), which
converts text to lowercase, or upper(string).
So you'd execute a query:
select * from table ORDER BY lower(lastname);
>
>
> Thanks,
>
> Lynna
> --
No prob. :)
Luis
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Case sensitivity when searching for and displaying data
@ 2003-09-04 06:17 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Lynna Landstreet <lynna@gallery44.org>
3 siblings, 1 reply; 6+ messages in thread
From: Christopher Kings-Lynne @ 2003-09-04 06:17 UTC (permalink / raw)
To: Lynna Landstreet <lynna@gallery44.org>; pgsql-php
> I've gotten a simple PHP search page working on the artists database that
> I've been developing these past few months, but I'm running into a few
> problems with PostgreSQL's case sensitivity.
No, you've been used to MySQL case insensitity methinks...
> Ideally I'd like the search text to case insensitive, so that if the user
> enters "jane smith", "Jane Smith" or "JANE SMITH", they'll still find the
> record for Jane Smith. But I'm not sure how to do that.
Use ILIKE:
SELECT * FROM table WHERE name ILIKE 'jane smith';
Or LOWER:
SELECT * FROM table WHERE LOWER(name) = LOWER('JANE SMITH');
(it'll help you to make a functional index in this case)
> Secondly, the case sensitivity thing is also messing up the order of the
> displayed results. Right now I have them set to order by lastname, and
that
> makes any names that begin with a lower case character come at the end of
> the list because of the order of the ASCII values of the letters, rather
> than where they would normally be in alphabetical order. Does anyone know
if
> there's a way around this?
Use LOWER:
SELECT * FROM table WHERE LOWER(name) = LOWER('JANE SMITH') ORDER BY
LOWER(name);
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Case sensitivity when searching for and displaying data
@ 2003-09-18 19:52 Lynna Landstreet <lynna@gallery44.org>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 6+ messages in thread
From: Lynna Landstreet @ 2003-09-18 19:52 UTC (permalink / raw)
To: pgsql-php
Many thanks for all the replies on this topic - I've been away from the
gallery for a bit, but now I'm back and this is all going to be very
helpful.
Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-09-18 19:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-03 22:31 Case sensitivity when searching for and displaying data Lynna Landstreet <lynna@gallery44.org>
2003-09-03 23:18 ` Rod Taylor <rbt@rbt.ca>
2003-09-03 23:20 ` Robby Russell <rrussell@commandprompt.com>
2003-09-04 02:40 ` Luis H <pgsql-php@geekhouse.no-ip.com>
2003-09-04 06:17 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-09-18 19:52 ` Lynna Landstreet <lynna@gallery44.org>
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