Operator

Function

AND

Using this operator always narrows a search.

Example: x AND y = both the condition rows criteria will be considered when filtering the objects.

OR

Unlike AND, OR always broadens a search.

Example: x OR y = one condition row will be considered at least.

NOT

NOT always excludes records with the specified term.

Operator

Function

like

Wildcard operator. Two SQL standard wildcard characters can be used: '%' represents any number of characters, '_' represents one character.

not like

Negation of the "Like" operator above.

starts with

A wildcard operator automatically assuming the '%' wildcard at the end of the expression.
Example:
Select the 'starts with' operator and type:

'dis' to search for all entries starting with 'dis' (disrespectful, disenchant, disembark...)
'%respect' to search for all entries containing 'respect' (disrespectful, irrespective, respectfully...)

'like', 'not like', 'start with': See section 'Wildcards in 'like' / 'not like' and 'start with' queries' in the 'Condition Operators' topic for information on using wildcards with these operators.

contains

Enables you to search for precise or fuzzy (less precise) matches to single words and phrases. It searches the words within a certain distance of one another.

Precondition:

Before you can use this operator, a full text index (FTI) must be created for the selected string attribute. This is done on the Attribute tab of the Object Type Customizer.

Main features:

- Speeds up the search process significantly.

- Unlike the "like" operator, it cannot search on the level of individual characters but works with whole words only.

- It does not support wildcards such as *, %, & etc.

- It is case insensitive.

- Enables to combine words using the boolean operators (AND, OR and NOT). Note that Valuemation assumes there is an AND operator between search terms if no boolean operator is specified.

Example:

Searching for a phrase:

The query 'System name contains SW Recycled' returns the following item: 'USU-SW Recycled' (provided there is the 'USU-SW Recycled' system available in the database).

Note that there is a sequence expected in the query. For example, when you specify the query as 'System name' contains 'USU Recycled', no item is returned provided there is only the 'USU-SW Recycled' system available.

Using the AND, OR and NOT boolean operators:

AND: Searches for results that include both the term before and the term after the operator. For example, the query 'system AND user' returns all items that contain both the word 'system' and the word 'user'.

OR: Searches for results that include either the term before or the term after the operator (or both). For example, the query 'system OR user' returns all items that contain either the word 'system' or the word 'user' (or both).

NOT: Searches for results that do not include the term after the operator. For example, the query 'system NOT user' returns all items that contain the word 'system' but do not contain the word 'user'.

equals

Search for an exact match.

not equals

Search for all entries except the exact match.

smaller

Search for entries with value smaller than the specified search value.

equals or smaller

As above, value equal to or smaller.

greater

As above, value greater.

equals or greater

As above, value equal to or greater.

between

Search for entries with value between two values - in the normal alphabetical or numerical sequence. In this special case, two comparison values are needed. These two values represent the upper and lower limits of the range.

in

Search for entries with value matching one of values contained in a defined set. In this special case, a list or set of discrete values is specified. This list needs to represent a range or sequence or any other obvious grouping. Logically this is an equivalent of a group of conditions related to the same attribute and connected with OR.

is null

Null is a special value in SQL which represents a state of being unknown or irrelevant. For a numerical attribute e.g. there is a difference between the known value of zero and the special value of NULL. The same is true of character fields: blank is a valid string and is not the same as NULL. This special operator is used to locate attributes which have this special value.

not Null

See the explanation of Null above. Use this operator to search for entries with a valid numerical or character value.

in current interval

Search for entries with datetime value within the specified interval which includes the current date: 'Today', 'This week', 'This month', 'This quarter', 'This year'.

in next interval, in previous interval

Search for entries with datetime value within the specified interval in the future or in the past. The length of the time interval is defined by selecting a "unit" and a number of these "units". Checkbox 'incl.' can be used to include or exclude the current day.

For example, to search for all systems whose validity will expire within the next 14 days including today, select the 'in next interval' operator and specify '2', 'Calendar Week(s)', 'incl.=true'.

sound like

This operator descends from a database SOUNDEX operator. SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.

The phonetic representation is defined as follows:

1. Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.

2. Assign numbers to the remaining letters (after the first) as follows:

b, f, p, v = 1

c, g, j, k, q, s, x, z = 2

d, t = 3

l = 4

m, n = 5

r = 6

3. If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.

4. Return the first four bytes padded with 0.

char can be of any of datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The return value is the same datatype as char.

sound not like

This operator descends from a database SOUNDEX operator. It works as a negation of the "sound like" operator.