----- Original Message ----- From: "Lynna Landstreet" <lynna@xxxxxxxxxxxxx> To: <pgsql-php@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx> To: <pgsql-php@xxxxxxxxxxxxxx> 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
