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

I used to use Access (several years ago) and LIKE was case insensitive back then. Many tricks had to be used to get Access to do case sensitive comparisons. From your post, I suspect that they've changed it around a bit in the past couple of revisions. For the record, there *are* some databases out in the world that use an extension called ILIKE (case-Insensitive LIKE). mSQL used CLIKE for their case-insensitive LIKE. In the olden days, I am told that even Postgres used a ~* operator. Confusing, huh? Perhaps Access uses one of them? So now, instead of jumping up and down on standards (which clearly were needed for something as simple as this LIKE fiasco), I find myself extremely curious... David, can you try ILIKE on Access? Or perhaps CLIKE? If they've implemented it with something like this, it would forego needing to do some fancy tricks like Options 1 & 3 and put all the processing within the DB server, hopefully speeding things up a bit. I want to know the results, too! Doug At 04:04 PM 8/18/00 -0700, David B. Small wrote: >...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 > ... snip ...