Have you ever needed to pick through an array, grabbing values that, perhaps, match a certain pattern, or grabbing values that do not match a particular pattern? For example, imagine that you have an array with the values: "Scott," "Fred," "Steve," and "Bob." Imagine that you were only interested in the array elements that contain the letter "s". If so, you can write your own function to snip out the particular array elements, or you could use a handy, little-known VBScript function: Filter. Filter has the following definition:

- continued -

FilteredArray = Filter(InputArray, ValueToFilterOn[, GetValuesThatMatch[, Compare]])

Let's take a look at each of the inputs for the Filter function. The first input, InputArray needs to be a single-dimension string array. This is the array that you want to filter. ValueToFilterOn is the filter string. GetValuesThatMatch is an optional boolean parameter that has a default value of True. If GetValuesThatMatch is True, the array elements in InputArray that contain the string ValueToFilterOn are returned; if GetValuesThatMatch is False, the array elements in InputArray that do not contain the string ValueToFilterOn are returned. Finally, Compare determines if one should perform a binary comparison (vbBinaryCompare) or text comparison (vbTextCompare) when filtering the array. (In short, vbBinaryCompare does a case-sensitive comparison whereas vbTextCompare does a case-insensitive comparison.)

The Filter function returns a single-dimension array with those values in InputArray that meet (or don't meet, depending on GetValuesThatMatch) the string specified by ValueToFilterOn. Now that we've dissected the Filter function, let's look at some sample code!

'Create an array of names Dim aNames aNames = Array("Scott", "Steve", "Yves", "Charles", _ "Ian", "Mike", "Christopher", "Roger", _ "Josh", "Kevin", "David", "Isaac", "John") 'List the complete list of names: Response.Write "<b>The name list contains:</b><br>" Response.Write join(aNames, ", ") 'Now, filter on a certain character Response.Write "<p><b>The names that contain the " & _ "letter 's':</b><br>" Dim aNamesWithS aNamesWithS = Filter(aNames, "s", True, vbTextCompare) Response.Write join(aNamesWithS, ", ") 'Now, filter and display on another character Response.Write "<p><b>The names that <i>do not</i> " & _ "contain the letter 'c':</b><br>" Response.Write join(Filter(aNames, "c", False, vbTextCompare), ", ")

[View the live demo!]

Note that we start the above script by creating an single-dimensional array of various names. Next we list all of the names using join. (For more information on join be sure to read: Parsing with join and split!) Next, we create another array, aNamesWithS, and populate it with the results from the Filter function that grabs only array elements that contain the letter "s". Finally, using the Filter function we display a list of elements in aNames that do not contain the letter "c".

Filtering Recordsets with Filter
While filtering a static array may seem neat, it's really not that useful. However, imagine if we could use this function to filter a particular column of a Recordset? Well, we can, with a little tweaking. First off, we'll need to use the GetRows method of ADO, which returns a Recordset in a two-dimensional array form. It is important to understand the physical makeup of such an array returned by GetRows. Imagine that we executed the following code:

'Assume there exists an opened Connection object, objConn Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open "SELECT ID, Name FROM Employee", objConn Dim aEmployees aEmployees = objRS.GetRows()

The above chunk of code opens a Recordset object, grabbing the ID and Name columns from the Employee table. Next, an array, aEmployees is created and populated with the results from the Recordset. Imagine that our Employee table contains three employees. Then our resulting array might look something like:

1	Scott Mitchell
2	Bill Gates
3	Steve Jobs

Note that each row in the array represents a row in the Recordset object, while each column in the array represents a column in the Recordset. Now, imagine that we wanted to display all the Employees that contain the letter "s". We could do that via Filter, but remember that Filter expects a single-dimensioned array. Therefore, we need to convert the second column in the array into a single dimensional array. We can use this nifty function to do just that:

Function CreateArrayFromColumn(aSlurp, iCol) 'Returns a one dimensional array based on the column iCol 'in aSlurp, a 2d array Dim iLoop, strResult For iLoop = LBound(aSlurp, 2) to UBound(aSlurp, 2) strResult = strResult & aSlurp(iCol, iLoop) & ")(*&^%" Next 'Hack off the last delimiter (thanks Bill Wilkinson!) strResult = Left(strResult, Len(strResult) - Len(")(*&^%") ) 'Now, return an array CreateArrayFromColumn = split(strResult, ")(*&^%") End Function

The function expects two parameters: the two-dimensional array, aSlurp, that was created via a GetRows call, and iCol, which is the column that you wish to convert into a single-dimension array. Now that we have this array back, we can use Filter on it! I have setup a live demo illustrating how this can work. Understand that you're not filtering EVERY column in the array returned by GetRows; rather, you're converting a particular column in the GetRows-returned array into a single-dimension array, and then filtering that array.

Feedback from Bill Wilkinson

I think this is pretty silly way to do it, in any case. This way would be much faster: Function CreateArrayFromColumn(aSlurp, iCol) 'Returns a one dimensional array based on the column iCol 'in aSlurp, a 2d array Dim iLoop, Result(), max max = UBound(aSlurp,2) ReDim Result(max) For iLoop = 0 To max Result(iLoop) = aSlurp(iCol, iLoop) Next CreateArrayFromColumn = Result End Function Sorry, but it seems pretty braindead to create a string in order to create an array when you can create the array more easily and faster. And without needing to worry about the content of elements, at all.

Well, I hope this article has been both interesting and educational. Filter is one of the lesser-known VBScript functions available. For information on some other not-too-mentioned VBScript functions, be sure to check out: Rarely Used VBScript String Functions. You may also wish to check out the technical docs for the Filter function.

Happy Programming!