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:
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), ", ")
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.
