SQL LIKE Clause Case Sensitive in ColdFusion MX Query-of-Query : Kinky Solutions : A Student's Perspective by Ben Nadel

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>
<cfquery name="qQOQ" dbtype="query">
SELECT
id,
name
FROM
qSQL
WHERE
name LIKE '%T%'
</cfquery>

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

Related Blog Entries

SQL Server Text Matching Is Case INSENSITIVE

Null Pointers Are Another Name For Undefined Values

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

Posted by Bob Silverberg on Apr 19, 2007 at 4:03 PM

@Bob,

That is a good suggestion. Thanks for posting it up here.

Posted by Ben Nadel on Apr 19, 2007 at 5:58 PM

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.

Posted by James Allen on Aug 15, 2007 at 10:34 AM

@James,

No problem. Just glad to help. If you ever hit any road blocks, feel free to contact me.

Posted by Ben Nadel on Aug 15, 2007 at 10:37 AM

I inherited some cold that uses this technique and it is not working.

Any suggestions?

Posted by MBoea on Nov 17, 2008 at 9:27 AM

Post Comment | Ask Ben

SQL LIKE Clause Case Sensitive in ColdFusion MX Query-of-Query : Kinky Solutions : A Student's Perspective by Ben Nadel