Simple Query Operators

You can use several simple query operators to define query criteria.

Note: Query operators are reserved in Siebel query language. If you enter a query value that includes a query operator, then you must enclose the query value in double quotes. For example, a query for records containing the text call is closed fails. The word IS falls into the category of reserved Siebel query language because it is used in the query operators IS NULL, IS NOT NULL, and so on. If you enclose the text call is closed in double quotes (“call is closed”), then the Siebel application returns all records containing the text call is closed . Other reserved operators include apostrophe (’), brackets ( [] ), comma (,), parentheses (()), tilde (~), and comparison operators such as = (equals), < (less than), and > (greater than).

The following table describes simple query operators. In this table, operators appear in uppercase but they do not have to be in uppercase. Query strings are case-sensitive. You can use simple query operators on their own.

This table describes and provides an example of each simple query operator.

Operator

Description

Example

*

(asterisk)

Wildcard operator. Placed anywhere in a string, returns records containing the string or containing the string plus any additional characters, including a space, that appear at the location of the asterisk.

You cannot use an asterisk to find dates.

To find words on more than one line in a field, you must use an asterisk to separate the words. You cannot query for control characters or non-printable characters, such as line feeds (LF) or carriage returns (CR).

*rang* finds the following: arrange, arranged, orange, orangutan, range, ranges, ranging, rang, strange, stranger, strangest, strangle, wrangle, and so on.

If performance is poor when you use the asterisk, then substitute "IS NOT NULL" in the query field. This substitution often improves performance.

?

(question mark)

Wildcard operator. Placed anywhere in a string, returns records containing the characters in the string plus any one additional character that appears at the location of the question mark.

?rag finds the following: brag, crag, or drag.

t?pe finds type and tape, but not tripe.

""

(double quotation marks)

Placed before and after a string, returns records that exactly match the string, unless modified by a wildcard operator (* or ?). Quotes find a group of words in the exact order with the exact uppercase or lowercase lettering.

"Oracle Solaris" finds records that contain Oracle Solaris in the query field.

=

(equals)

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 negates wildcard operators within the query value.

For CIAI-enabled fields, if you type "=abc*", then the query becomes a case-sensitive query because you use an equal sign (=) as an operator in the query.

<

(less than)

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 earlier than 20 June 2001. When entering a date, use the format that is specific to your implementation.

>

(greater than)

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.

<>

(not equal to)

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.

<=

(less than or equal to)

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.

>=

(greater than or equal to)

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 does not start with Smi.

IS NULL, is null

Placed in the query field, returns records for which the query field is blank.

IS NULL in the Due Date query field finds 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.

IS NOT NULL in the Due Date query field finds all records for which the Due Date field is not blank.

~

(tilde)

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 might affect performance.

You can enter a CIAI query expression for a contact as follows: [Last Name] ~Like abc*