Filter Directory Contents By Multiple File Extensions

Query of Queries really helped me out this morning. The Model-Glue team is working on a ColdFusion Builder extension and we need to get a list of files to inspect. There could be many types of files in a project that aren't relevant to our purposes and I wanted to filter only the ones I need.

My Directory query contained lots of SVN specific files, which make for a good example of what we want to filter, before doing our inspection work.

Original Directory Query

What we need in this case is to filter anything that isn't a CFM, CFC or XML file. The CFDirectory tag will allow only a single filter, so what do we do?

In ColdFusion, I would use listlast( filename, ".") to look at the file extension but that would mean I'd have to unpack the query and either make a new one, or pack it in another datastructure. Waste of code, I tell ya. I ended up using Query of Queries and a LIKE statement to filter.

New Directory Query

The Code I Used for the Query of Queries

view plain print about
1<cfquery name="CleanedDirectoryQuery" dbtype="query">
2    SELECT *
3    FROM arguments.sourceDirectoryQuery
4    WHERE lower(NAME) LIKE '%.cfm'
5        OR lower(NAME) LIKE '%.cfc'
6        OR lower(NAME) LIKE '%.xml'

The secret sauce is the lower() function on NAME, and the LIKE conditional with a wildcard % operator. I found this through the Query of Queries documentation and I also learned about a few other wildcard operators as well.

List of Wildcard Operators Supported In Query of Query LIKE conditional

  • The underscore (_) represents any single character.
  • The percent sign (%) represents zero or more characters.
  • Square brackets ([ ]) represents any character in the range.
  • Square brackets with a caret [^] represent any character not in the range.
  • All other characters represent themselves.

Simple and maintainable. Just how I like it. Thanks #ColdFusion!

There are no comments for this entry.

Add Comment Subscribe to Comments

12/23/10 9:23 AM # Posted By Marc Esher

you can, in fact, specify multiple filters by using a pipe:


12/23/10 9:40 AM # Posted By Dan Wilson

Wow. Multiple filters pipe delimited does work. This is contrary to the documentation: ( )

filter - Optional if action = "list"
File extension filter applied to returned names, for example, *.cfm. One filter can be applied.

Furthermore, the documentation notes: The filter attribute specifies a pattern of one or more characters. All names that match that pattern are included in the list. On Windows systems, pattern matching ignores text case, on UNIX and Linux, pattern matches are case-sensitive.

Which could be problematic for those who don't always name their extensions in lower case. (We'll get them one day, those terrible terrible people).

How'd you find this out, might I ask?


12/23/10 9:50 AM # Posted By Marc Esher

I found it out a few years back on someone's blog. No idea now though. Probably Ben Nadel. Or maybe Dan Switzer.

Interestingly enough, I just noticed that Dan Vega has a cookbook entry on it:

12/23/10 1:25 PM # Posted By Bill Berzinskas

you should check this blog from Ben Nadel, apparently you can use pipes to seperate multiple filters..

1/28/11 12:03 PM # Posted By Matthew Abbott

Just wanted to add 2 cents. you could do *.cf*|*.xml to handle cfc,cfm, and xml.

Add Comment Subscribe to Comments