Bookshelf v8.0: Query Operators

When you create a query, you use operators, or conditions, which are used to search the database for matching records. In Table 7 and Table 8, operators are shown in uppercase; however, query strings are not case-sensitive, and the operators do not have to be in uppercase.

There are simple queries and compound queries. Simple query operators can be used on their own. When you perform a compound query, you must use parentheses to control the order in which the search for matching records is performed.

Table 7. Simple Query Operators
Operator	Description	Example
*	Wildcard operator. Placed anywhere in a string, returns records containing the string or containing the string plus any additional characters at the position at which the asterisk appears, including a space.	rang finds arrange, arranged, orange, orangutan, range, ranges, ranging, rang, strange, stranger, strangest, strangle, wrangle, and so on. You cannot use * to find dates. To find words on more than one line in a field, you should use * to separate the words. You cannot query for control characters or nonprintable characters, such as line feeds (LF) or carriage returns (CR).
?	Wildcard operator. Placed anywhere in a string returns records containing the characters specified in the string plus any one additional character which appears at the location of the question mark.	?rag finds brag, crag, or drag, but not bragging. t?pe finds type and tape, but not tripe.
""	Surrounds a string that, unless modified by a wildcard (* or ?), must be matched exactly. Quotes let you query for a group of words in its exact order.	"Sun Solaris" finds records that contain Sun Solaris in the query field.
=	Placed before a value, returns records containing a value equal to the query value.	=Smith finds all records for which the value in the query field is Smith. It also turns off wildcards within the query value.
<	Placed before a value, returns records containing a value less than the query value.	<6/20/01 finds all records in which the value of the query field is before 20 June 2001. When entering a date, use the format that is specific to your implementation.
>	Placed before a value, returns records containing a value greater than the query value.	>5/31/01 finds all records in which the date in the query field is later than 31 May 2001. When entering a date, use the format that is specific to your implementation.
<>	Placed before the value, returns records containing a value that is not equal to the query value.	<>6/20/01 finds all records in which the date in the query field is not 20 June 2001. <>Paris finds all the records in which the value in the query field is not Paris.
<=	Placed before a value, returns records containing a value less than or equal to the query value.	<=500 finds all the records in which the value in the query field is less than or equal to 500.
>=	Placed before a value, returns records containing a value greater than or equal to the query value.	>=500 finds all records in which the value in the query field is greater than or equal to 500.
NOT LIKE, not like	Placed before a value, returns records not containing the value.	NOT LIKE Smi* finds all records in which the value in the query field do not start with Smi.
IS NULL, is null	Placed in the query field, returns records for which the query field is blank.	Enter IS NULL in the Due Date query field to find all records for which the Due Date field is blank.
IS NOT NULL, is not null	Placed in the query field, returns records for which the query field is not blank.	Enter IS NOT NULL in the Due Date query field to find all records for which the Due Date field is not blank.
~	Placed before LIKE and a value with a wildcard operator, returns all matching records regardless of case.	~LIKE Smi* finds all records in which the value in the query field starts with Smi, smi, SMI, and so on. Using this operator may affect performance.

NOTE: If performance is poor when you use the asterisk (*), substitute "IS NOT NULL" in your query. This often improves performance, and will return the same sets of records.

Table 8. Compound Query Operators
Operator	Description	Example
AND, and	Placed between values, returns only records for which all the given conditions are true.	performance AND memory finds all records that contain both performance and memory in the query field.
OR, or	Placed between values, returns records for which at least one condition is true.	performance OR memory finds all records that contain either performance or memory in the query field. performance* OR memory* finds all records that start with either performance or memory in the query field.
NOT, not	Placed before a value, returns only records that do not contain the value.	performance AND NOT LIKE memory finds all records that contain performance but not memory in the query field. NOT (performance OR memory) finds all records that contain neither performance nor memory in the query field.
()	Surrounds the values and operators that will be processed first, regardless of the default processing order.	(sun OR moon) AND NOT stars returns records that contain sun or moon, but not stars, in the query field.
LIKE, like	Placed before a value, returns records containing the value.	(performance* OR memory) AND LIKE (problem) finds all records in which the query field starts with performance* or memory and also includes problem. Note that the LIKE operator is case sensitive. To find matches regardless of case, see (~).