I just ran into an interesting issue when querying the file system. I was trying to get only parts of the file set by using a ColdFusion MX query-of-queries (QoQ) and limiting the results based on file extensions. For example, I had a query like this:
<cfquery name="qFiles" dbtype="query"> SELECT name FROM qAllFiles WHERE name LIKE '%.jpg' </cfquery>
In the above example, I was trying to get a sub-set of files that were JPGs. This worked great until I notices that I wasn't get any of the files that had uppercase extensions like Blam. I had thought that the SQL LIKE clause was NOT case sensitive; however, I usually work directly in SQL so maybe there was a difference in QoQ.
After running these tests:
<cfquery name="qSQL" datasource="#REQUEST.DSN.Source#"> SELECT id, name FROM blog_entry WHERE name LIKE '%T%' </cfquery> <!--- qSQL : Returns 15 Records. ---> <cfquery name="qQOQ" dbtype="query"> SELECT id, name FROM qSQL WHERE name LIKE '%T%' </cfquery> <!--- qQOQ : Returns 7 Records. --->
I discovered that there was a difference in how LIKE was handled. In theory, the two queries above should have returned the same result set as they were running the same query (the only difference being that the first query would return a sub-set of the universe of records while the second query would return the entire available result set). If you are running a query in the database then yes, the LIKE clause is case insensitive. If you are running a query of queries in ColdFusion MX 7, then yes, the LIKE clause is case sensitive.
Want to use code from this post? Check out the license.