SET functions

SET functions test for one or more values within a SET or SETQ column.

Before you begin

Syntax

SET{CONTAINS[ANY | ALL] | EXCLUDES}(<set-column>,<value>)

Arguments

Argument	Description	Additional information
`SETCONTAINS`	Test for a value found in `<set-column>`
`SETCONTAINSANY`	Test for any values found in `<set-column>`	Values must be comma-separated and grouped within square brackets `[...]`
`SETCONTAINSALL`	Test for all supplied values found in `<set-column>`	Values must be comma-separated and grouped within square brackets `[...]`
`SETEXCLUDES`	Test a `<value>` is not found in `<set-column>`
`<set-column>`	`SET` or `SETQ` column name	`SET` and `SETQ` data types
`<value>`	`ID` or `STRING` literal value	* ID data type * STRING data type

Additional information

Function operators

SET functions work with AND and OR logical operators

FeatureBase returns no results where operator logic is incorrect

Returns

Values returned depend on the position of the SET function within the SELECT query:

Position	Returns	Example	Additional information
Within `<select-list>`	True or False	`SELECT <set-function> (<set-column>, <value>) FROM <table-name>;`	SELECT list
Within `WHERE` clause	Row	`SELECT * FROM <table-name> WHERE <set-function> (<set-column>,<set-value>)`	WHERE clause

Examples

Segments table

create table segments
    (_id id, segment stringset);
insert into segments(_id, segment)
    values (1, ['RED', 'BLUE', 'GREEN']);

+-----+------------------+
| _id | segment          |
+-----+------------------+
|   1 | [RED BLUE GREEN] |
+-----+------------------+

Testing set membership in the select list

This query returns true.

SELECT SETEXCLUDES(segment, 'purple')
  AS NOTPURPLE
  FROM segments;  

Testing set membership as a where clause filter

This query returns true

SELECT _id FROM segments WHERE
  SETEXCLUDES(segment, 'purple');

Testing set membership as a where clause filter with multiple set members

This query returns true with the selected _id.

SELECT _id FROM segments WHERE
  SETEXCLUDES(segment, 'purple') AND
  SETEXCLUDES(segment, 'yellow');