How to use the LIKE operator, wildcard and special characters in your IBM FileNet Content Platform Engine Sweep's Filter Expression

Body

A sweep is an instance of a background service that you configure to process objects in a database table or some other set of items. To optimize sweep performance, you can optionally make the filter expression for the sweep a part of the WHERE clause of the sweep query. This inclusion means that the sweep retrieves only those database rows that satisfy the filter conditions.
The criteria is expressed in terms of properties and values of the objects that are targeted by the sweep. The syntax is a subset of the SQL syntax for the WHERE clause.

Here are some examples of filter conditions:

All superseded documents: VersionStatus = 4
All documents that were created at least a year ago: DateCreated < NOW() - TimeSpan(365, 'Days')
All content in a specific storage area: StorageArea = OBJECT('{5E2BE09A-F4B1-49E2-A229-77FE32E5FEF1}')
Complex logical expression: VersionStatus = 4 AND DateCreated < NOW() - TimeSpan(365, 'Days') AND ContentSize > (1024 * 1024 * 500)

Patern matching searches can be done using the LIKE operator and the '%' wilcard character. The example below returns all objects where the document title begins with the letter 'S'.
 

DocumentTitle LIKE 'S%'

The NOT operator can be used when the condition is not true. The example below returns all objects where the document title does not begin with the letter 'S'.
 

not(DocumentTitle LIKE 'S%')

Special characters like '~' and '$' can be escaped with the '\' character. The example below returns all objects where the document title begins with '~$S'.
 

DocumentTitle LIKE '\~\$S%'

This information is also included in the following technote.

/support/pages/node/562375