Multivalued (ARRAY) Comparisons in SharePoint Search SQL Syntax
Applies to: SharePoint Server 2010
In this article
No Quantifier Specified
All Quantifier
SOME (or ANY) Quantifier
Examples
Compares columns that have multiple values (multivalued columns) that are stored in the content.
…WHERE <column> <comp_op> [<quantifier>] <comparison_list>
Remarks
SharePoint Server search returns an error if the column reference is not a multivalued column. The column data type must be compatible with the elements of the comparison list. If necessary, you can cast the column reference as another data type.
You can use any of the usual comparison operators in the WHERE clause. In a multivalued comparison, the comparison operators have slightly different meanings, depending on whether you use a quantifier, and which one you use. For this reason, we describe the functions of the comparison operators in the specific quantifier tables that follow.
The comparison list specifies an array of literal values that are compared against the multivalued column. Following is the syntax for the comparison list:
ARRAY [<literal>,<literal>]
Note
Be aware of the comparison list syntax. You must surround the group of literals that make up the comparison list with square brackets. Do not surround individual elements of the comparison list with square brackets. For example, ARRAY [1,2,3] is correct syntax, but ARRAY [1[,2][,3]] is not.
The method used to determine whether the multivalued comparison returns TRUE or FALSE is specified by the optional quantifier. The following sections describe each quantifier, and how each comparison operator functions when you use that quantifier.
No Quantifier Specified
If you do not specify a quantifier, each element on the left (L) side of the comparison is compared to the element in the same position on the right (R) side. The comparison begins with the first element in the arrays, and progresses through the last element. If all the elements on the L side are equivalent to the corresponding elements on the R side, then the number of array elements is used to determine which array is greater.
The following table shows the operation of the comparison operators when no qualifier is specified, and provides a brief description of each.
Operator |
Description |
|---|---|
= |
"Equal to" returns TRUE when each L element has the same value as the corresponding R element, and both arrays have the same number of elements. |
!= or <> |
"Not equal to" returns TRUE when one or more L elements have values that differ from the corresponding R elements, or when the L and R arrays do not have the same number of elements. |
> |
"Greater than" returns TRUE when the value of each L element is greater than the value of the corresponding R element. If all the L element values exactly match the corresponding R elements, and the R array has elements with no corresponding L elements, "greater than" returns TRUE. |
>= |
"Greater than or equal to" returns TRUE when the value of every L element is greater than or equal to the value of the corresponding R element. |
< |
"Less than" returns TRUE when the value of each L element is less than the value of the corresponding R element. "Less than" also returns TRUE when the L side has fewer elements than the R side. |
<= |
"Less than or equal to" returns TRUE when the value of every L element is less than or equal to the value of the corresponding R element. |
All Quantifier
The ALL quantifier specifies that each element in the left (L) side is compared against every element on the right (R) side. To return TRUE, the comparison must be true for all elements on the L side when compared to every element on the R side. The number of elements in the L and R array sides has no effect on the result.
The following table shows how each comparison operator functions with the ALL quantifier.
Operator |
Description |
|---|---|
= |
"Equal to" returns TRUE when each L element value is the same as every R element value. |
!= or <> |
"Not equal to" returns TRUE when one or more of the L element values is different from any of the R element values. |
> |
"Greater than" returns TRUE when every L element value is greater than every R element value. |
>= |
"Greater than or equal to" returns TRUE when every L element value is greater than or equal to every R element value. |
< |
"Less than" returns TRUE when every L element value is less than every R element value. |
SOME (or ANY) Quantifier
You can use the SOME quantifier and the ANY quantifier interchangeably. The SOME quantifier specifies that each element on the left (L) side is compared against every element on the right (R) side. To return TRUE, the comparison must be true for at least one of the elements on the L side when compared to every element on the R side. The number of elements on the L and R side arrays has no effect on the result.
The following table shows how each comparison operator functions with the SOME quantifier.
Operator |
Description |
|---|---|
= |
"Equal to" returns TRUE when at least one of the L element values is the same as any of the R element values. |
!= or <> |
"Not equal to" returns TRUE when none of the L element values is the same as any of the R element values. |
> |
"Greater than" returns TRUE when at least one of the L element values is greater than any one of the R element values. |
>= |
"Greater than or equal to" returns TRUE when at least one of the L element values is greater than or equal to any one of the R element values. |
< |
"Less than" returns TRUE when at least one of the L element values is less than any one of the R element values. |
Examples
The following comparisons all evaluate TRUE. Remember that in actual use, the search query syntax requires the left side to be a property, not a literal value.
ARRAY [1,2] > ARRAY [1,1]
ARRAY [1,2] > ARRAY [1,1,2]
ARRAY [1,2] < ARRAY [1,2,3]
ARRAY [1,2] = SOME ARRAY [1,12,27,35,2]
ARRAY [1,1] != ALL ARRAY [1,2]
ARRAY [1,20,21,22] < SOME ARRAY [0,40]
ARRAY [1,20,21,22] < ANY ARRAY [0,40]