between_ci(X;Y;Z)

Returns a boolean value indicating whether a given value is between two other values and is case insensitive. (Available as of version 12.34)

Syntax

between_ci(X;Y;Z)

Input

Argument Type Description
X any simple type The value to test within the range defined by Y and Z
Y any simple type The minimum value of the range in which to check for X
Note: The order of Y and Z doesn't matter.

If Y is the name of a column, Z must also be the name of a column.
Z any simple type The maximum value of the range in which to check for X
Note: The order of Y and Z doesn't matter.

If Z is the name of a column, Y must also be the name of a column.
Note: All arguments must be of the same type. This function is most commonly used with numeric values; however, if strings are provided as arguments, the comparison is done as a binary string comparison.

Return Value

Returns an integer value of 1 if either:
  • X>=Y and X<=Z
  • X>=Z and X<=Y
Returns a 0, otherwise.

If X is N/A, the result is 0, unless either Y or Z (or both) are N/A, in which case the result is 1.

If X, Y, and Z are not either all numeric values or all string values, an error is returned.

Sample Usage

value begin end between_ci(value;begin;end)
2 1 3 1
2 3 1 1
1.5 1 3 1
1.5 2.5 3.1 0
1.5 1.2 3.1 1
'bat' 'Bar' 'baz' 1
'Bag' 'bar' 'baz' 0
'Foo' 'bar' 'baz' 0
1 3 0
1 1

Example

In the "Sales Item Detail" table (pub.demo.retail.item), you can find only those rows whose values in the Item SKU column fall between 'A' and 'D'. To do this, create a computed column and apply the between_ci(X;Y;Z) function to the sku column, where Y is 'A' and Z is 'D':
<base table="pub.demo.retail.item"/>
<willbe name="between_result" value="between_ci(sku;'A';'D')"/>

For those values in the sku column that fall between 'A' and 'D', the result is 1. Otherwise, the result is 0.

Additional Information

between_ci(X;Y;Z) is identical to between(X;Y;Z) except that it performs case-insensitive (in the English alphabet) comparisons.