#IN
This operator asks if the left argument, a database field, contains any of the values listed in the right argument. 1 is returned if the field value occurs in the list; otherwise, 0 is returned.
Syntax:
database field #IN list
Examples:
If the database field type is coded or numeric, then List is a parenthesized list of numbers separated by commas:
STATUS #IN (1,2,9)
PAYRATE #IN (17.9,18.2)
Note that for coded fields, you must use the field’s numeric codes (which are shown in the F1 expression help) rather than the character labels for the field.
If the database field type is social security number, then List may, optionally, include dashes:
SSN #IN (756-45-3432,156-47-5364,345-36-4554) or
SSN #IN (756453432,156475364,345364554)
If the database field type is date, then List is a parenthesized list of dates:
DOR #IN (1/1/1990,2/15/1989,12/1/2008)
If the database field type is character, then List is a single pattern (i.e., a string of text surrounded by single quotes) or a parenthesized list of patterns. The values of character fields can be compared to patterns of specific character values (‘SMITH’,’JONES’) or patterns containing special characters (‘SMI*’,’JO*’). For example,
NAME #IN ‘SMITH’
NAME #IN (‘SMITH’,’JONES’)
NAME #IN ('SMI*’,’J*’,’*ERS')
The first expression selects records for which the Name field contains the value SMITH; in other words, the expression returns 1 when Name is SMITH and 0 when it is not. The second expression selects records for which the Name field contains either the value SMITH or the value JONES. The third expression selects records for which the NAME field contains a value among the patterns of the list. For details about special characters, such as the one (*) in our example, see the discussion of patterns.
Case differences are ignored (‘SMITH’ vs. ‘Smith’ or ‘smith’).
If the right argument to #IN is empty (just two adjacent single quotes, perhaps separated by a blank space), then records with blank field values are selected. That is, the patterns ‘’ and ‘ ‘ match a string of blank characters. This is how you select missing values of a character field. Note that the double quote character (“) cannot be used as a substitute for two adjacent single quotes.
If a pattern in the right argument contains a single quote, you must double it in the argument to #IN, as in ‘That”s the ticket’. Double quote characters have to be quadrupled in strings; see the discussion of patterns.
Related Operators: