Table 25. 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).
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.
|
?
|
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.
|
