I've had this situation before - a mySQL query using 'like' to search a string value for a given phrase sometimes becomes case sensitive when the query contains other 'Where' phrases. ( As far as I know, 'like' is supposed to be case INsensitive, and this is a known bug in some installations of mysql - I've tried updating mysql drivers and still it remains). But, regardless of the cause, there's an easy fix...

In this case I am working on a CartWeaver ecommerce site, and the simple product search in the admin is not behaving quite like I'd expect. Searching for "blue" does not return a product with "Blue" in the title (note the capital B), and vice-versa. To fix this, I found a simple method to force both the entered value and the query comparison string to be all-lowercase, which removes any issue of capital letters making a difference.

The original search code and resulting query go something like this

view plain print about
1<cfif URL.find NEQ "">
2    <cfset QueryFind = URL.find>
3</cfif>

( then the query )

view plain print about
1SELECT [some fields]
2FROM tbl_products
3WHERE tbl_products.product_Name
4 LIKE '%#QueryFind#%'

My fix was to simply force the original search string to lowercase with ColdFusion

view plain print about
1<cfif URL.find NEQ "">
2    <cfset QueryFind = lcase(URL.find)>
3</cfif>

And then in the query, use mySQL's "lower()" function to do the same to the compared string value

view plain print about
1SELECT [some fields]
2FROM tbl_products
3WHERE LOWER(tbl_products.product_Name)
4 LIKE '%#QueryFind#%'