To read the article online, visit

Filtering Arrays with Filter!

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:

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:

1Scott Mitchell
2Bill Gates
3Steve 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) & ")(*&^%"

 '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)
  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!

Article Information
Article Title: Filtering Arrays with Filter!
Article Author: Scott Mitchell
Published Date: Monday, November 13, 2000
Article URL:

Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers