Operator

Function

like

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

Example:
Select the 'like' operator and type:
'respect' to search for all entries containing exactly 'respect'
'%ful' to search for all entries ending with 'ful' (helpful, disrespectful,...)
'dis%' to search for all entries starting with 'dis' (disrespectful, disenchant,...)
'%respect%' to search for all entries containing 'respect' (disrespectful, irrespective, respectfully...)

not like

Negation of the "Like" operator above.

Example:
Select the 'not like' operator and type:
'respect' to search for all entries which are not exactly 'respect'
'%ful' to search for all entries not ending with 'ful'

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...)

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 defined for the selected string attribute. This is done on the Attribute tab of the Object Type Customizer.

Main features:

- 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 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". Check box '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'.

Note that the 'Incl.' option is not available if 'Days' is selected as interval unit.

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.