Filters
To select records in a table, you must use Filters. For information about selecting records, and for examples of Filters being used, see Retrieve a Record. This section describes how to create Filters.
Basic Comparisons
Normally, you can use operators like '==', '!=' and '<' to perform a single comparison, and determine whether two objects are equal, not equal and so on. Filters allow you to perform the same types of comparison, using the same operators, for every record in a table.
For example, if you have a Table object called table
, the following expression creates a Filter that matches all records with "MyValue" in column A:
table.Column("A") == "MyValue"
A | B |
---|---|
MyValue | XYZ |
MyValue | MyValue |
XYZ | XYZ |
XYZ | MyValue |
The '==' operator can also be used between columns. The following example matches all records where the value in column A is equal to the value in column B:
table.Column("A") == table.Column("B")
A | B |
---|---|
MyValue | XYZ |
MyValue | MyValue |
XYZ |
XYZ |
XYZ | MyValue |
You can use the ‘!=’, ‘<’, ‘<=’, ‘>’ and ‘>=’ operators in the same way. Using Table.Column
at one side of these operators creates a Filter.
NOTE: Table columns can be of integer type or of string type. If the ‘<’, ‘<=’, ‘>’, or ‘>=’ operators are used between integer columns the comparison is numeric. If used between string columns, the strings are compared using the numeric value of each character. If string columns have been used to store numbers, a string comparison is still used.
Combine Filters
Filters can be combined using the ‘&’ and ‘|’ operators, or negated using the ‘!’ operator.
The ‘&’ operator specifies rows that match both filters. The following example selects all records where column A is equal to “MyValue” and column B is equal to “XYZ”:
table.Column("A") == "MyValue" & table.Column("B") == "XYZ"
A | B |
---|---|
MyValue | XYZ |
MyValue | MyValue |
XYZ |
XYZ |
XYZ | MyValue |
In a similar way, the ‘|’ operator specifies rows that match either filter:
table.Column("A") == "MyValue" | table.Column("B") == "XYZ"
A | B |
---|---|
MyValue | XYZ |
MyValue | MyValue |
XYZ |
XYZ |
XYZ | MyValue |
The Like Operator
The ‘%’ operator tests whether the value in a column matches a string using wildcards. It tests that a column contains a matching string value, using the ‘%’ character as a wildcard that can match zero or more of any character, and using the '_' character to match any single character. For example, the following filter matches records where column A contains a string beginning “DEF-”, followed by any string, a “-” and then any single character:
table.Column("A") % @"DEF-%-_"
This would match the following rows:
A | B |
---|---|
DEFABC | XY\Z |
DEF-ABC-! | XY\Z |
DEF-123-% | XY\Z |
If you want to match a literal ‘%’ character you can escape it using ‘\’, for example:
table.Column("A") % @"DEF-%-\%"
With that expression the second “-” must be followed by a literal ‘%’ character. This matches the following rows:
A | B |
---|---|
DEFABC | XY\Z |
DEF-ABC-! | XY\Z |
DEF-123-% | XY\Z |
To match the “\” character, escape it with another “\”. To match the value in column “B” you would use this filter:
table.Column("B") % @"XY\\Z"
Match All Records
To match all records in a table you can use the empty filter:
Filter.Empty
Filter Examples
The following examples assume you have a Table object called “table”, and that it has three columns called “A”, “B” and “C”:
Filter | Meaning |
---|---|
table.Column("A") == "MyValue"
|
Column “A” is equal to “MyValue” |
table.Column("A") == "MyValue"
& table.Column("B") != new DateTime(1980, 1, 1)
|
Column “A” is equal to “MyValue” and column “B” is not equal to 01/01/1980. |
table.Column("A") < table.Column("B") |
(table.Column("C") % "Tr%" &
!(table.Column("D") >= 37))
|
Column “A” is less than column “B”, or both column “C” begins with the string “Tr” and column “D” is less than 37. (The '! ' operator before "(table.Column("D")... " means that the condition reads "not" greater than or equal to 37). |