`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.