Thread: 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);
Attachment
----- 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