SQL LIKE Clause Case Sensitive in ColdFusion MX Query-of-Query
Posted June 14, 2006 at 8:10 AM
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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Other Searches | Print Page
Newer Post
Null Pointers Are Another Name For Undefined Values
Older Post
ColdFusion SQL Error - [Table ....] Is Not Indexable By Name
Reader Comments
I just encountered this issue and found a solution that allows for case-insensitive searching with QofQ's. I know this is an old blog entry, but I thought I'd share it anyway.
You can use a combination of LOWER() and LCase() to address this issue.
In your example, your query would change to:
SELECT id, name
FROM qSQL
WHERE LOWER(name) LIKE '%#LCase("T")#%'
Now it doesn't make much sense just using "T", but if you were using a variable, which is more likely, then it will convert your variable to lower case, and also convert the contents of the column (in your example "name") to lower case as well. This version would look like:
SELECT id, name
FROM qSQL
WHERE LOWER(name) LIKE '%#LCase(my_Variable)#%'
Cheers,
Bob
@Bob,
That is a good suggestion. Thanks for posting it up here.
Superb post!
Thanks for the tip. I only just came across this issue as I don't often use QoQ.. I was quite stumped for a few minutes trying to figure out why the search wasn't working as expected.
2 minutes later and solution found via Google.. Thanks for this blog (which has helped me more than once via the essential Google programming problem search) and the commenter for the fix.
@James,
No problem. Just glad to help. If you ever hit any road blocks, feel free to contact me.
I inherited some cold that uses this technique and it is not working.
Any suggestions?
Hi,
Thanks for post this excellent tip here.
Nice one, Ben. This had me tripped up for a bit today. This blog entry lets me know I'm not alone, even if I am three years behind the curve.
Josh
I just finished modifying an object factory to fail over to a WDDX file of cached serialized queries when the database is unavailable (fun stuff, maybe worth a blog post!) and encountered this, since the non-DB mode was doing all the queries in QoQ mode. What a pain. I wouldn't mind the case sensitivity if I could just turn it off perhaps with a cfquery tag attribute.
Thanks for the post man.
@Josh,
Yeah, serializing data for queries can be a pain! Especially since the exact data types are not brought back through (from what I can remember, although that might not be completely accurate). When I stored to WDDX, I remember having to also store meta data about the query to have it deserialize properly (via manually construction). Total pain!
is a like also posible in a case ?
Hi Ben,
I found this post very useful thanks. Over the past three or four years since I started learning CFML I've come across solutions to my programming problems on your blog countless times but never seem to get round to saying thanks... You're a real asset to the CFML community. Thanks very very much for all the past posts, this post and all the other posts I'll no doubt encounter in the future!
Ben Roberts
(Probably the only person using CFML in the Czech Republic)
@Ben,
Ben (awesome name by the way), I'm always glad to help. I just hope people get as much out of it as I do - writing all this stuff really helps me learn.
Hi Ben,
I found another similar difference in QoQ.
Like '%a%' is treated differently in a database query and a QoQ. Database query will return anything with an 'a' in it, QoQ will not return results where the only occurrence is that they start with 'a'.
ie. Database query will return 'apple, banana'
QoQ will only return 'banana'.
Great Blog by the way, always look for it first in search results.
Ignore that last one, I was getting my queries case mixed up. Sorry.
@Robin,
No problem - glad you got it sorted.
