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

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.

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.

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)

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.

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.

@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. :)

@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.