Siebel Developer's Reference > Operators, Expressions, and Conditions >

Pattern Matching with LIKE and NOT LIKE


NOTE:  The Search Engine Table property for View and Applet must be based on the same table as the index. For example, if the search index is based on S_EVT_ACT, then the view should be based on Action and the applet should be based on Action.

The LIKE operator is used in character string comparisons with pattern matching. The syntax is as follows:

char1 LIKE char2

where char1 is the value to be compared with the pattern and char2 is the pattern to which char1 is compared. The NOT logical operator can be used in conjunction with LIKE to exclude patterns. The syntax including the NOT logical operator is:

char1 NOT LIKE char2

or

NOT (char1 LIKE char2)

While the equal ( = ) operator does exact matching, the LIKE operator matches a portion of one character value to another. Patterns can use special characters to denote different characters. These characters are given in the following table.

Character
Purpose
Example

*

Zero or more characters

[Last Name] LIKE "Sm*" would return all records whose [Last Name] value starts with the characters "Sm", as in "Smith", "Smythe", "Smart", and so on.

[Last Name] LIKE "*om*" would return all records whose [Last Name] field contains the characters "om", as in "Thomas", "Thompson", "Tomlin", and so on.

?

One character

[First Name] NOT LIKE "Da?" would return all records whose [First Name] value was three characters long and did not start with the letters "Da". Records with "Ted", "Tom", and "Sam" would be returned, but "Dax" and "Dan" would not.

NOT ([First Name] LIKE "?o?") would return all records whose [First Name] value was three characters long and did not have as its middle character "o". Records with "Ted" and "Sam" would be returned, but "Tom" and "Bob" would not.

Character is case-sensitive when executing pattern matching. In addition, the parentheses are required when including the NOT logical operator (the second variation of the NOT syntax).

NOTE:  On occasion, using the wildcard * to find all entries in a field can cause a performance problem. If it does, use IS NOT NULL instead. For more information, see NULL.

Siebel Developer's Reference