|
|
|
|
|
The search condition operators in Column Search are represented by symbols. They can be selected from a list activated by clicking the appropriate symbol.The selection of operators is "attribute-sensitive" which means that the list of operators offers only operators relevant to the specific attribute it processes.
In User Settings and Global Settings, you can specify a default string operator. For details, please see the 'Basic Settings' topic in User Settings and Global Settings.
Note: The setting in User Settings always has a higher priority than the setting in Global Settings.
|
Description of available condition operators:
Operator |
Function |
like |
Wildcard operator. Two SQL standard wildcard characters can be used: '%' represents any number of characters, '_' represents one character. Example: |
not like |
Negation of the "Like" operator above. Example: |
starts with |
A wildcard operator automatically assuming the '%' wildcard at the end of the expression. Example: 'dis' to search for all entries starting with 'dis' (disrespectful, disenchant, disembark...) |
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: All search conditions are connected with 'AND' operator. The structure for combining AND / OR / NOT is not available. |
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. |
|
|
Wildcards in 'like' / 'not like' and 'start with' queries
The following special characters can be used as wildcards with operators 'like' / 'not like' and 'start with':
- % (percent sign)
The percent symbol represents any number of arbitrary characters (i.e. zero, one or more characters).
- _ (underscore)
The underscore symbol represents just one arbitrary character.
Expression '%_%' will search for all records with at least one arbitrary character in the given column.
- # (hash sign)
Hash sign can be used to search for other special characters. For example, use #% to search for an actual percent symbol in the text. And yes, you can use ## to search for a hash sign in the text.
Examples:
Let's consider a text snippet: "The company registered a 15% sales increase."
Using the 'like' operator, searches for entries containing in the given column parts of the text could be conducted in the following way:
- '%#% sales increase'
Searches for any text ending with "% sales increase".
- 'The company registered a 15#%%'
Searches for any text starting with "The company registered a 15%".
- '%#%%'
Searches for any text containing the percent sign, including the percent sign alone (with no characters before or after).
Searching in the 'Translations of Application Text' catalog
The 'Standard Translation' column in catalog 'Translations of Application Text' displays a transient attribute. Its values are calculated in runtime and written using Escape codes. Consequently it is not possible to search for umlaut characters in the catalog.
Workaround: The above described limitation of umlaut characters not being searchable can be circumvented by 'intelligently' substituting the umlaut characters with the '%' wildcard character. For example: When searching for text 'Übersicht über alle IT Assets', type '%bersicht %ber alle IT Assets' instead. Note that even though we are substituting a single umlaut character, using the undescore wildcard (which represents exactly one arbitrary character) would not be sufficient here as the umlaut is actually represented by six characters (hexadecimal escape code).