Обсуждение: Case sensitivity when searching for and displaying data

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

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

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);

----- 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

> 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

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

Odd request.

I am not a developer myself, but would like to know what kinds of technical
interview questions (and possible answers) I can ask developers to try to
find someone that really knows what they are doing.

I need to hire a postreSQL/php developer for some maintenance and upgrade
work on my site.

The site is fairly sophisticated. I think it was built on postreSQL 6X and
PHP. When we loaded to the server, we ended up with 7X and there seems to be
a few wrinkles with the code on the newer version.

A few of the main issues that I am going to need this person to deal with
are:


1) On one custom form, I am getting the error - "no OID was passed"

2) Being able to search "non-case sensitive"  AND being able to hold the
order of the results from page to page.

3) Using an "apostrophe" anywhere in any of the forms, creates a page error

4) Every time I edit one of the forms with a url, an extra "http://" gets
put into the address

5) On several forms, if you have not completed all the information, and you
get an error message, ALL of the info that was input during that session, is
cleared.


I am nervous to have a complete stranger working on the code, but the
original developer is no longer available.

I am guessing most of these should be fairly straight forward fixes.

So any help on screening questions that will help me find a developer
capable of solving these problems, is most appreciated.

Regards,

Brion