Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2009 (Minneapolis, MN) with: Kurt Wiersma

SQL LIKE Clause Case Sensitive in ColdFusion MX Query-of-Query

By Ben Nadel on
Tags: ColdFusion, SQL

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.




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

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.

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!

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.

I've come across a similar issue, though not with like. From what I am experiencing, CF doesn't handle distincts unless the case is the same (as with your issue using LIKE).

For example, I am doing the following QoQ:

<cfquery name="qryUseUnits" dbtype="query">
SELECT OptionDesc,OptionValue
FROM qryUseUnits

UNION

SELECT Unit AS OptionDesc, Unit AS OptionValue
From qMatSup
</cfquery>

The problem is that in qryUseUnits, there is a OptionValue of "Cubic Feet" and in qMatSup, there is a value "Cubic feet".

When doing the distinct UNION, CF doesn't consider those distinct. So, I figured I would try to implment something like this:

<cfquery name="qryUseUnits" dbtype="query">
SELECT OptionDesc, OptionValue
FROM qryUseUnits

UNION

SELECT Unit AS OptionDesc, Unit AS OptionValue
FROM qMatSup
WHERE UPPER(Unit) NOT IN (SELECT UPPER(OptionDesc) AS OptionDesc FROM qryUseUnits)
</cfquery>

... but then I can't seem to get the SELECT inside of the IN conditional to work. Maybe it's been a long day and the query syntax is incorrect.

@Tristan,

Query of queries should be OK with the IN() construct; but, it doesn't support sub-selects. Perhaps you might try using the IN() with the value list:

WHERE UPPER( Unit ) NOT IN ( #UCase( valueList( qryUseUnits.optionDesc ) )# )

You'll run into problems if you have any values that have commas in them; but, if it's numeric values, this might just do the trick.

@Ben,

Actually, according to Adobe's API, sub-selects in an IN conditional are supported... or so it says (you know how that goes):

"This conditional lets you specify a comma-delimited list of conditions to match. It is similar in function to the OR conditional. In addition to being more legible when working with long lists, the IN conditional can contain another SELECT statement."

I still couldn't get it to work, however, I did end up getting it to work similar to what you mentioned:

<cfset arMatUnits = listToArray(uCase(valueList(qryUseUnits.OptionValue)))>
<cfquery name="qryUseUnits" dbtype="query">
SELECT OptionDesc,
OptionValue
FROM qryUseUnits
UNION
SELECT Unit AS OptionDesc,
Unit AS OptionValue
FROM qMatSup
WHERE UPPER(Unit) NOT IN (<cfqueryparam value="#listAppend(arrayToList(arMatUnits), 0)#" list="true" cfsqltype="CF_SQL_VARCHAR">)
</cfquery>

I convert it from a list to an array and then back to a list again just in case there happened to be an empty or NULL value that would be returned from the valueList() function.

Worked perfectly. :)

@Tristan,

I'll have to look more into this sub-select concept. I have *never* seen this work in a sub-select. It's definitely possible that I have just never done it right in a query of queries??

@Ben,

Well, you wouldn't be the only one that can't get it to work unless Adobe lied in their documentation and said it's possible to do them when really it isn't. In my previous post, I pasted a quote from Adobe's docs, but I have never been able to find an example of doing it (I assumed it was just like your typical SQL statement). Oh well.

I am using QoQ concept in my programming, to get the distinct values from a query. But, it was retriving all the records. My code is as below.
<cfquery dbtype="query" name="Distinct_dept">
SELECT DISTINCT department FROM get_all_departments
ORDER BY department
</cfquery>

Any help is greatly appriciated.

@Sankar,

In query of queries, I typically use a GROUP BY rather than a distinct. Try that?

  • SELECT
  • department
  • FROM
  • get_all_departments
  • GROUP BY
  • department
  • ORDER BY
  • department