Case Sensitivity and Query of Queries


I posted the other day about a fun bug with ColdFusion's query of a queries randomly deciding that a value was “like” NULL.  Well, I also discovered that when you use LIKE in Query of a Query, the text comparison is case sensitive.  This follows what some RDBMS, like PostgreSQL, does, but goes against what others, like MySQL (mostly), does. Now, this isn't a bug, so I'm not going rag on Adobe about how it functions. It makes sense as it does seem to be mostly standard in SQL.  This is even mentioned by Adobe, though its rather buried in the “Developer's Guide” (which I suspect few long time developers read for new versions, versus just checking the tag library).

So let's look at the effects this can have on your applications functionality.  Here we have a query that pulls a list of all the anime titles in a database.  Let's say we want to find all titles with the word “the” in it.



<cfquery name="GetStudioTitlesWithThe" dbtype="query">
    SELECT title
    FROM GetMovies
    WHERE title LIKE '%The%'
</cfquery>

Notice it found 24 results…but if we change the query a bit….



<cfquery name="GetStudioTitlesWiththe" dbtype="query">
    SELECT title
    FROM GetMovies
    WHERE title LIKE '%the%'
</cfquery>

….there are 7 others that never came up in the first results, including some of my favorite titles. Now, that is a kind of silly example, but say you wanted to find all customers named “William” in a database? If yours are anything like mine, they seem in capable of properly capitalizing their names, so your search could exclude some important results and you might never know that “william jefferson clinton” had ordered from your site! Of there, there are times where this is the ideal behavior and you do want case sensitive searching. But for more “casual” searches, as I call them, you usually don't.

So how do you get your Query of a Queries to do case-insensitive searching, when you want it it to?  The same way you would do it in a regular SQL statement, by using the UPPER() or LOWER() functions on both sides of your comparison so that they are all the same case.  Fortunately, Adobe made sure both functions are available in the Query of Queries for just this purpose.



<cfquery name="CorrectedGetStudioTitlesWithThe" dbtype="query">
    SELECT title
    FROM GetMovies
    WHERE lower(title) LIKE '%#LCASE("The")#%'
</cfquery>

And now we have all 31 results. I think this discovery is also a good reminder of why it is important when doing your queries, to keep case sensitivity in mind.  MySQL sees LIKE as case insensitive, unless you specifically collate the field to a binary type when you create the table, or cast it to a binary collation type while running your query.  Most others see LIKE as case sensitive, and you either have to the lower trick noted above to get around it, or use a DB specific function like PostgreSQL's awesome ILIKE. Newer developers don't always realize this, though, nor do those used to the more standard SQL behavior of comparisons being case specific unless explicitly done otherwise. I've seen more than one web application built with a MySQL back end whose developers were among that group and so password matches were case insensitive (defeating the purpose of having rules like must have one upper case and all).  Just something to keep in mind while doing your queries, to ensure you actually get the results you think you should.