The other day while I was walking to work I found myself day dreaming. As my thoughts wondered I dreamt of a perfect world where you could use regular expressions whenever, wherever you liked.
Once arriving at work I was back to reality not everything in life can be a regular expression!
As I worked away writing some boring queries, I figured I might liven up my day and try using regular expressions to improve a query. After some research, much to my surprise I found that MySQL could handle regular expressions… maybe my day dream was coming true.
In my case I was looping over a list and comparing with another list found within the database, previously I had been using numerous ‘LIKE’ statements to match the two lists (see below)
<cfquery name="qData" datasource="#application.dsn#">
SELECT *
FROM tableName
WHERE status != 'Archive'
AND
<cfset iCount = 1 />
<cfloop list="#arguments.groups#" index="i">
<cfoutput>
(availability LIKE '#i#,%' OR availability LIKE '%,#i#' OR availability LIKE '%,#i#,%' OR availability = #i#)
</cfoutput>
<cfif iCount LT listLen(arguments.groups)>
<cfoutput> OR </cfoutput>
</cfif>
<cfset iCount = iCount + 1 />
</cfloop>
ORDER BY #arguments.sort# #arguments.sortOrder#
</cfquery>
After some messing about with regexp (and some help from my boss) I was able to reduce this to a simple regular expression so my query became.
<cfquery name="qData" datasource="#application.dsn#">
SELECT *
FROM tableName
WHERE status != 'Archive'
AND
<cfset iCount = 1 />
<cfloop list="#arguments.groups#" index="i">
<cfoutput>
availability REGEXP '[^0-9]*(#i#)[^0-9]*'
</cfoutput>
<cfif iCount LT listLen(arguments.groups)>
<cfoutput> OR </cfoutput>
</cfif>
<cfset iCount = iCount + 1 />
</cfloop>
ORDER BY #arguments.sort# #arguments.sortOrder#
</cfquery>
Next time you are writing a query take a second to ask yourself is this a good time to try out regexp? and hopefully the answer will be yes.
