Filtering and Command Conditions
Conditions for filtering and executing commands
There are two application areas for the use of conditions:
- As a criterion for filtering data rows at input or output time, see ±filter.
- As a prerequisite for the execution of commands, see ±if/else.
All filtering and condition evaluating is field-based. It takes place line by line according to the general mode of operation of CSVfox, and is always based on the current data of the actual line.
The evaluating is mostly described by a comparison of two operands. Occasionally only one is tested (whether it is empty), or more are needed (for a range expression).
Evaluation modes for filters and conditions
Generally, condition evaluation will be either be in numeric mode or in text (string) mode. Which one will be used, is determinated by the left operand: round brackets or a number hints a numeric operation, otherwise a string operation will be conducted.
Additionally, there are some mode parameters for the way the evaluation shall work:
/n forces numeric evaluation of the operands
/a forces string-type evaluation of the operands
/i only for strings: ignore upper/lower case
/c only for strings: use culture-aware comparison of strings
Examples
Some filter examples
- +filter(#)>(3)
- Reads the current data row only if input line number is larger than 3.
I.e., the first three rows of the input file wil be omitted. - +filter[3]
- Reads and processes this data row only if the third column is not empty.
- -filter[Status]!={Locked}
- Outputs the current data row only if the column "Status" is not equal to the text "Locked".
- -filter/i[Fruit]={Apple}/{Pear}
- Outputs the current data row only if the text in column "Fruit" equals either "Apple" or "Pear", or any equivalent text regardless of upper or lower case.
- -filter(NumericExpression1)>(NumericExpression2)
- Outputs the current data row only if NumericExpression1 is larger than NumericExpression2.
"NumericExpression1" and "NumericExpression2" are calculated before comparison takes place.
Some conditional command examples
- -if["Not in Navigation"]=Yes//set[*]=1//else//set[*]=0
- If the field "Not in Navigation" contains the value "Yes", sets it to "1", otherwise to "0".
- -if[ThisField]={Expression}//set[ThatField]=Yesvalue//else//set[ThatField]=Novalue
- Inserts in column [ThatField] either "Yesvalue" or "Novalue", if column [ThisField] matches "Expression", else if not.
"Expression", "Yesvalue" and "Novalue" are resolved. For the conditions see below. - -if(NumericExpression1)>=(NumericExpression2)//set[Field]=1//else//set[Field]=2
- In column [Field], this inserts either "Yesvalue" or "Novalue" if "Expression1" is greater or equal to "Expression2", else if not.
"Expression1", "Expression2", "Yesvalue" and "Novalue" are resolved.
Condition Operands
Operands
An operand can be a single literal CSV field [Field], a numeric CSV field ([Field]), or a numeric expression a or formula (Expression), or a literal string or an expression {Expression}.
So there is always some kind of brackets around any left operand.
The right operand(s) do not require the {Expression} curly brackets.
Before evaluating the condition, all fields are replaced by their actual values, all formulas are calculated, and all expressions are resolved. For details, see also Expressions
In the following table, "{Opn}" stands for an operand of any kind, be it a single field, or of numeric, literal string, or expression type.
The left operand is always a single operand, while on the right side there may be more operands (see below).
{Op} = left operand
{Op1} = first right operand
{Op2} = another right operand ...
Table of Condition Operators
Available Condition Operators
| Function | Operator | Alternative Operator |
String | Numeric | Number of Right Operands |
Syntax |
|---|---|---|---|---|---|---|
| {Op} not empty | ✓ | ✓ | 0 | {Op} | ||
| {Op} empty | ! | n: | ✓ | ✓ | 0 | {Op}! |
| {Op} is null | % | u: | ✓ | ✓ | 0 | {Op}% |
| {Op} equal {Op1} | = | e: | ✓ | ✓ | 1 * | {Op}={Op1} |
| {Op} greater than {Op1} | > | g: | ✓ | ✓ | 1 * | {Op}>{Op1} |
| {Op} lesser than {Op1} | < | l: | ✓ | ✓ | 1 * | {Op}<{Op1} |
| {Op} greater or equal than {Op1} | >= | ge: | ✓ | ✓ | 1 * | {Op}>={Op1} |
| {Op} lesser or equal than {Op1} | <= | le: | ✓ | ✓ | 1 * | {Op}<={Op1} |
| {Op} begins with {Op1} | <# | b: | ✓ | 1 * | {Op}<#{Op1} | |
| {Op} finishes with {Op1} | #> | f: | ✓ | 1 * | {Op}#>{Op1} | |
| {Op} contains {Op1} | <#> | c: | ✓ | 1 * | {Op}<#>{Op1} | |
| {Op} regular matches {Op1} | @ | r: | ✓ | 1 * | {Op}@{Op1} | |
| {Op} greater than {Op1}, and lesser than {Op2} |
<> | gl: | ✓ | 2 | {Op}<>{Op1}/{Op2} | |
| {Op} greater/equal to {Op1}, and lesser/equal to {Op2} |
<=> | gel: | ✓ | 2 | {Op}<=>{Op1}/{Op2} | |
| {Op} lesser than {Op1}, or greater than {Op2} |
>< | lg: | ✓ | 2 | {Op}><{Op1}/{Op2} | |
| {Op} lesser/equal to {Op1}, or greater/equal to {Op2} |
>=< | leg: | ✓ | 2 | {Op}>=<{Op1}/{Op2} |
* more operands can be used when concatenating them with delimiters, see below
As you see, there are two different operators available for each condition, the standard form and the alternative, alphabetical form.
Both operators do the same. While the standard form might be better readable and distinctive, using of the alphabetic version can be necessary e.g. when the commands are directly typed on the command line, and the command processor tends to misinterpret the comparison character, like the "<" and the ">".
Negation
All operators can also be used in a negated way.
Negations is expressed by prepending a "!" or a "n" before the operator.
So "=" or "e:" means "equal", while "!=" or "!e:" means "not equal" or "unequal" (as well as "n=" or "ne:", you can mix the variations).
In many cases, a negated operator is equivalent to another non-negated operator.
So, the negation of ">" (greater) is "!>" (not greater), which is equivalent to "<=" (lesser or equal).
But this does not apply to the literal operators "b:" (begins with), "c:" (contains), and "f:" (finishes with); here the negation "!b:" or "nb:" means "string does not begin with", "!c:" or "nc:" means "string does not contain", and "!f:" or "nf:" means "string does not end with".
Also the regular match "r:" can be negated with "nr:" or "!@" (or with a mix of both), which then means "string does not match the regular expression".
However, there is still an important difference when it comes to more than one operand on the right side.
Concatenated right operands
For all conditions that require one right operand, there can be used an operand list instead. An operand list consists of several operands concatenated with a single delimiter (generally a "/").
In this case, the condition will be evaluated with potentially each of those right operands, one after the other.
The following rules apply:
- In a plain, unnegated condition, multiple right operands are OR-ed. That means if at least one of the right operands lets the evaluation be true, the whole condition resolves to true.
- In a negated condition, multiple right operands are being AND-ed. That means that only if all of the right operands lets the evaluation be true, the whole condition resolves to true.
Note the difference and how you can use it for your purposes.
An example with the numerical filter condition "lesser than":
| -filter([Nnn])<([A]) | Plain condition with one right operand: The numeric value of field [Nnn] is lesser than the numerical value of field [A]. |
| -filter([Nnn])<([A])/([B])/([C]) | Plain condition with multiple right operands: Field [Nnn] is numerical lesser than field [A] or lesser than field [B] or lesser than field [C]. |
| -filter[Nnn]!>=([A]) | Negated condition with one right operand: The numeric value of field [Nnn] is not greater or equal; i.e. ist is also lesser than field [A]. |
| -filter([Nnn])!>=([A])/([B])/([C]) | Negated condition with multiple right operands: [Nnn] is numerical lesser than field [A] and also lesser than field [B] and also lesser than field [C]. |
Another example with the string filter condition "contains":
| -filter[Xyz]c:abc | Plain condition with one right operand: Field [Xyz] contains the literal "abc". |
| -filter[Xyz]c:abc/def/ghi | Plain condition with multiple right operands: Field [Xyz] contains "abc" or "def" or "ghi". |
| -filter[Xyz]nc:abc | Negated condition with one right operand: Field [Xyz] does not contain the literal "abc". |
| -filter[Xyz]nc:abc/def/ghi | Negated condition with multiple right operands: Field [Xyz] does not contain "abc" and also not "def" and also not "ghi" (i.e., nor "abc" neither "def" neither "ghi"). |
| filter/:[Xyz]!c:abc:def:ghi | The same as above, but with a changed separator (":" instead of "/"). While the ":" of the operator now looks like the separator ":", this is no real problem. |
If there is an "empty" operand, you must place it first or last, so that the list of operands seems to start or end with a delimiter. Do not place it between delimiters, because this will be interpreted as a double delimiter which marks the start of next condition or command.
Also do not forget to "escape" all brackets that are not a grammatical part of a field identifier, formula or formatting expression, i.e. replacing all literal (, ), [, ], {, }, <, > characters with \(, \), \[, \], \{, }, \<, or \>.
Case-insensitive mode for strings
For case-insensitive comparisons of strings, the "i" mode can be applied:
| -filter[Field]={expression} | [Field] and {expression} are equal. |
| -filter/i[Field]={expression} | [Field] and {expression} are considered as equal, but the comparison is not case-sensitive. |
| -filter[Field]!=[Another Field] | [Field] and [Another Field] are not equal. |
| -filter/i[Field]!=[Another Field] | [Field] and [Another Field] are not equal, even if comparison ignores case (i.e., is not case-sensitive). |