From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | "Lynna Landstreet" <lynna(at)gallery44(dot)org>, <pgsql-php(at)postgresql(dot)org> |
Subject: | Re: Case sensitivity when searching for and displaying data |
Date: | 2003-09-04 06:17:12 |
Message-ID: | 036b01c372ac$2dd63420$2800a8c0@mars |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Grant Henderson | 2003-09-05 08:28:07 | Re: Strange browser behavior when querying PostgreSql database |
Previous Message | Luis H | 2003-09-04 02:40:05 | Re: Case sensitivity when searching for and displaying data |