Arithmetic Expressions¶

There is no distinction between numeric and string variables. All values are (variable length) strings.

String constants must be in quotes, but numeric constants can be either quoted or not (since they can’t be mistaken for field names/variables).

Operators¶

Arithmetic Operators¶

Arithmetic operators perform mathematical operations on numeric operands. Attempting to do arithmetic on non-numeric variables results in a value filled with ?.

Operator	Name	Example	Result
`*`	Multiplication	`a * b`	The product of a and b.
`/`	Division	`a / b`	The division of a and b.
`%`	Remainder	`a % b`	The remainder of a/b.
`-`	Subtraction	`a - b`	The subtraction of b from a.
`+`	Addition	`a + b`	The addition of a and b.
`-`	Negation	`-a`	The negative of a.

Logical Operators¶

Logical operators perform boolean algebra on logical operands. Logical true has the value “1” and Logical false has the value “0”.

Operator	Name	Example	Result
`!`	Logical NOT	`!a`	The logical negation of a.
`&&`	Logical AND	`a && b`	The logical AND of a and b.
`\|\|`	Logical OR	`a \|\| b`	The logical OR of a and b.

Comparison Operators¶

Comparison operators evaluate to 1 if the condition is logically true and 0 if the condition is false. Comparison of two values which are valid numbers is an ordinary numeric comparison. If either operand is NOT numeric, then a character string comparison is performed.

Operator	Name	Example	Condition
`$`	Substring comparison	`a $ b`	b is a substring of a.
`<`	Less than	`a < b`	a is less than b.
`<=`	Less than or equal	`a <= b`	a is less than or equal to b.
`!=`	Inequality	`a != b`	a is not equal to b.
`==`	Equality	`a == b`	a is equal to b.
`LIKE`	Pattern matching	`a LIKE b`	a matches the pattern b.
`>=`	Greater than or equal	`a >= b`	a is greater than or equal to b.
`>`	Greater than	`a > b`	a is greater than b.

Patterns¶

The LIKE operator supports a pattern as its right operand. The % and _ characters are used as wildcards. The % wildcard matches zero or more characters until the next character in the pattern is found. If there are no more characters in the pattern then the % wildcard matches all remaining characters. The _ wildcard matches any character but will only match a single character.

Other Operators¶

Operator	Name	Example	Condition
`()`	Parentheses	`a * (b + c)`	Evaluates `b + c` first.
`()`	Function call	`a(b, c)`	Evaluates to the result of function a with arguments b and c.
`\|`	Concatenation	`a \| c`	Results in a string containing a followed by b.
`,`	List delimiter	`a , b`	Results in a list containing a and b.

Operator Precedence and Associativity¶

Operators are listed top to bottom, in descending precedence.

Precedence	Operator	Description	Associativity
1	`()`	Function call / Parentheses	Left-to-right
2	`*`	Multiplication	Left-to-right
2	`/`	Division	Left-to-right
2	`%`	Remainder	Left-to-right
3	`-`	Subtraction	Left-to-right
3	`+`	Addition	Left-to-right
3	`\|`	Concatenation	Left-to-right
3	`$`	Substring comparison	Left-to-right
4	`-`	Negation	Right-to-left
5	`<`	Less than	Left-to-right
5	`<=`	Less than or equal	Left-to-right
5	`!=`	Inequality	Left-to-right
5	`==`	Equality	Left-to-right
5	`LIKE`	Pattern matching	Left-to-right
5	`>=`	Greater than or equal	Left-to-right
5	`>`	Greater than	Left-to-right
6	`!`	Logical NOT	Right-to-left
7	`&&`	Logical AND	Left-to-right
8	`\|\|`	Logical OR	Left-to-right
9	`,`	List delimiter	Left-to-right

Alternative Operator Representations¶

The following operators have alternative representations that can be used instead.

Operator	Alternatives
`!=`	`#`
`==`	`=`
`LIKE`	`.LIKE.`
`!`	`NOT` `.NOT.`
`&&`	`AND` `.AND.` `&`
`\|\|`	`OR` `.OR.`

Functions¶

Substring(value, position, length)

Creates a substring of a string.
- Alias: ss
- Parameters:
  - value: The original string to create a substring from.
  - position: The 1 based index in value where the substring begins.
  - length: The maximum number of characters after position to include in the substring.
- Returns: A substring derived from the value parameter
- Example: The following evaluates to 1 (true):
```
Substring("ABCDEF", 2, 3) == "BCD"
```
Replace(value, search, replace)

Replaces characters within a string.
- Parameters:
  - value: The original string to search in.
  - search: A string in value that should be replaced.
  - replace: The string that replaces search.
- Returns: A string derived from the value parameter. Any matches of search have been replaced with replace.
- Example: The following evaluates to 1 (true):
```
Replace("abca", "A", "XX") == "XXbcXX"
```
Findstring(value, search)

Searches a string for a substring.
- Parameters:
  - value: The original string to search in.
  - search: A string in value to look for.
- Returns: The 1 based index in value where the first matching substring begins.
- Example: The following evaluates to 1 (true):
```
FindString("ABCDEF", "CD") == 3
```
Length(value)

Gets the length of a string.
- Alias: strlen
- Parameters:
  - value: The string to get the length of.
- Returns: The number of characters in the string.
- Example: The following evaluates to 1 (true):
```
Length("ABC") == 3
```
Abs(value)

Gets the absolute value of a number.
- Parameters:
  - value: The number to get the absolute value of.
- Returns: The absolute value of the number or the original value if it was a string.
- Example: The following evaluates to 1 (true):
```
Abs("-15") == 15
```
Max(value1, value2, …valueN)

Finds the largest value in a list. Accepts a variable number of arguments.
- Parameters:
  - value1: The first value to compare.
  - value2: The second value to compare.
  - valueN: The nth value to compare.
- Returns: If all values are numerical then the largest numerical value is returned. Otherwise if one of the values is a string the string that compares greater than the rest is returned.
- Example: The following evaluates to 1 (true):
```
Max(-3, 5, 4) == 5
```
  The following evaluates to 1 (true):
```
Max("A1", "A10", "A2") == "A2"
```
Min(value1, value2, …valueN)

Finds the smallest value in a list. Accepts a variable number of arguments.
- Parameters:
  - value1: The first value to compare.
  - value2: The second value to compare.
  - valueN: The nth value to compare.
- Returns: If all values are numerical then the smallest numerical value is returned. Otherwise if one of the values is a string the string that compares lesser than the rest is returned.
- Example: The following evaluates to 1 (true):
```
Min(-3, 5, 4) == -3
```
  The following evaluates to 1 (true):
```
Min("A1", "A10", "A2") == "A1"
```
Asc(value)

Gets the numerical ASCII value of the first character in a string.
- Parameters:
  - value: String to get the ASCII value of.
- Returns: The ASCII value of the first character in the string or 0 if value was empty.
- Example: The following evaluates to 1 (true):
```
ASC("ABC") == 65
```
Chr(value)

Converts a numerical ASCII value to a string.
- Parameters:
  - value: ASCII value to convert to a string.
- Returns: A string containing a single character that matches the ASCII value.
- Example: The following evaluates to 1 (true):
```
CHR(65) == "A"
```
If(condition, trueValue, falseValue)

Selects a value based on a condition.
- Parameters:
  - condition: The condition to test.
  - trueValue The value to return if true.
  - falseValue The value to return if false.
- Returns: trueValue if condition evaluates to 1. falseValue if condition evaluates to 0.
- Example: The following evaluates to 1 (true):
```
IF(1 < 2, "TRUE", "FALSE") == "TRUE"
```
  The following evaluates to 1 (true):
```
IF(3 < 2, "TRUE", "FALSE") == "FALSE"
```