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

Posted June 14, 2006 at 8:10 AM by Ben Nadel

Tags: ColdFusion, SQL

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.

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.

@Tristan,

I'll let you know if I come up with anything; I'm not hopeful though.

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?

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment