I encountered an unexpected behavior while trying to remove all records with hotmail email adresses from a query of some clients.

The query of queries was pretty simple:

view plain print about
1<cfset tmpQry = arguments.qSourceData>
2<cfquery name="returnQueryNoHotmail" dbtype="query">
3    select * from tmpQry
4    where email not like '%@hotmail.com%'
5</cfquery>
6<cfset rtnQuery = returnQueryNoHotmail>

 

The problem that I found was that it was removing SOME of the records with hotmail addresses, but not all. Upon further inspection of the data, I found that the records that were not being matched with this query all had Hotmail addresses in uppercase - for example: JOHN123@HOTMAIL.COM instead of john123@hotmail.com.

So the issue here is that the LIKE condition in a query of a query in ColdFusion 8 is case sensitive.

To fix this, and make sure that I get any other "hotmail.com" record, I used the LOWER() function, which would match either Hotmail.com, HOTMAIL.COM, hotmail.com, hotmail.COM or any other combination of the characters.

view plain print about
1<cfset tmpQry = arguments.qSourceData>
2<cfquery name="returnQueryNoHotmail" dbtype="query">
3    select * from tmpQry
4    where lower(email) not like '%@hotmail.com%'
5</cfquery>
6<cfset rtnQuery = returnQueryNoHotmail>

 

Additionally, you can also use regular expressions for your LIKE comparison. For example, if you want to get all clients whose first name started with the letter "E" through the letter "M", you could write it like this:

view plain print about
1<cfquery name="qOq" dbtype="query">
2    select * from qClients where first_name like '[E-Me-m]%'
3</cfquery>

Pretty cool!

You can see the full reference on query of queries here in the docs (which is where I found the answer):
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_7.html

 

This entry was posted on August 26, 2009 at 10:49 AM and has received 1961 views. There are currently 0 comments. Print this entry.