RE: [PHP-DB] Non case-sensitive SQL Query

...and I find myself back in the middle of it :) Here's what I've found out: Option 1) if you have the ability to control the data going *into* the database, you can ensure that everything is stored in upper or lower case (have one field called "field", and another called "field_matching"...so you can retreive "field" where "field_matching" LIKE $uppercase_string Option 2) Theoretically, you can do an UPPER(fieldname) LIKE UPPER('%string%'). Unfortunately, I wasn't able to get this to work out, syntactically. Now, before anyone claims compatibility or lack thereof of SQL92, and before someone worries that another's experience might not imply standard behavior...I'm using ODBC to connect to (predominately) MS Access 2000 databases. Option 3) If you know the different case situations you might run into (all upper, all lower, first character upper, first character in each word upper, etc.), it's not too difficult to write a function which returns an array of these text situations, given a string. You can then while(each()=list()){} through that array, running a query on each, or a query on WHERE (field=$st) or (field=$st2) etc. In my case, this was to computationally expensive. (I wrote it, but was dissatisfied with performance hits...my app is doing *lots* of queries, so multiplying each one by n is noticable.) If I were a betting man, I'd guess that search engines use Option 1, other folks use Option 2 or 3, depending on their tolerance for computer time and whether their DB supports 2. (I hope this clears me of wrongdoing for bringing things up 3 days ago...) -David > -----Original Message----- > From: Doug Semig [mailto:dougslist@c3net.net] > Sent: Friday, August 18, 2000 11:39 AM > To: php-db@lists.php.net > Subject: RE: [PHP-DB] Non case-sensitive SQL Query > > > DB2 is operating correctly and in a standards compliant > manner. It's not a > matter of being "by default case sensitive on queries," it's > a matter of > selecting a collating sequence when evaluating the LIKE predicate. > > We **just** talked about this! I mean like 3 days > ago...right here on this > list! Is it super-horrible to look at the archives? Maybe > someone should > get this question and the right answer in FAQTS (or whatever > it's called) > so we don't see it every few days. > > > http://marc.theaimsgroup.com/?l=php-db&m=96636906117977&w=2 > is a link to > first message of the 3 day old thread on this exact topic. > > The correct solution has already been given in this thread, > though not by > me this time. Use UPPER() in the SQL query. > > Doug > > Jeroen ten Berge was heard at 01:32 PM 8/18/00 +0200 to say: > >Is DB2 by default case sensitive on queries ? > >You could do an like 'Anders%' or like 'anders%' > > > > > >-----Original Message----- > >From: Anders Dannberg [mailto:anders.dannberg@edk.se] > >Sent: Friday, August 18, 2000 12:44 > >To: php-db@lists.php.net > >Subject: [PHP-DB] Non case-sensitive SQL Query > > > > > >Hi all! > > > >I need some help on how to make a NON case-sensitive sql > query. I use the > >following query which outputs what i need: > > > >$query = "SELECT * FROM user INNER JOIN maintable ON user.number = > >maintable.number WHERE name LIKE 'Anders%'"; > > > >However, as this is case-sensitive, it will not display, for > example records > >containing "anders", but only "Anders" etc. > >Is there a way i can make sql-queries NON case-sensitive > (without changing > >the already inputted data in the DB) > > > >Is there a way i can do this in within the code? Without any > changes to > >database settings... > >I'm using DB2, PHP4. > > > >Thanks in advance. > >Anders > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: > php-list-admin@lists.php.net > > >