Relational operators and wildcard symbols


Use relational operators or wildcard symbols in fields and in the advanced search bar to refine or widen the search results.

Relational operators

When you want to search for a value within a numerical range, relational operators are useful in non-text fields such as date and time fields.

Use the following relational operators as leading characters in fields in a form and in the advanced search bar:


Operator

Action

<

Matches contents that are less than the value.

>

Matches contents that are greater than the value.

<=

Matches contents that are less than or equal to the value.

>=

Matches contents that are greater than or equal to the value.

=

Matches contents that are exactly equal to the value.

!=

Matches contents that are not equal to the value.

For example, to search for all requests created after a certain date, use the greater than (>) relational operator and specify a date and time format. For example, > "July 5, 2020" in the Create Date field finds all requests created after July 5, 2020. If you don't specify the time, the search criteria defaults to 0:00:00, the start of the day.

Wildcard symbols

When you specify a search criteria to find requests, use the following wildcard symbols anywhere in a form to indicate one or more characters:

Wildcard

Function

% (Percent symbol)

Matches any string of 0 or more characters. For example: J%son matches Jackson, Johnson, Jason, and Json.

Additionally, includes leading or trailing characters in your search.

For example, to find all requests submitted by Jill Bobbington, Bobby Fenton, and Bob Comptonson with an Anywhere search, enter *Bob%ton* in the Submitter field. The search returns all requests for which the Submitter field contains the strings "Bob" and "ton" in that order with any number of characters leading, trailing, and in between.

You can also override a field's search style by using a leading percent sign.

For example, if the field's search style is Equal and you enter %Rob into the Submitter field, your search finds Robert Smith and Jim Robertson, not only equal matches to %Rob. However, if you use a leading percent sign, you lose any faster search times that would result from using the Equal or Leading search styles. See Search styles in character fields.

_ (Underscore)

Matches any single character. For example: B_b matches Bab, Bob, and Bub.

- (Hyphen)

Indicates a range. Always use within square brackets [ ].

[ ] (Square brackets)

Matches any single character within a specified range or set. For example, [a-f] matches the range of characters a through f, and [abcf] matches the set of characters a, b, c, or f.

Square brackets and the symbols associated with them do not work with Oracle databases.

[^] (Square brackets with caret)

Matches any single character not within a specified range or set. For example, [^a-f] matches all characters except the range a through f, and [^abcf] matches all characters except a, b, c, or f.


When used in a form, the percent sign (%), underscore (_), and open bracket ([) symbols always function as wildcard symbols except in the following situations, where they function as explicit characters:

  • When you specify a relational operator such as > or =.
  • When the field's default search style is Equal and you do not use a leading or trailing percent sign (%).

Example: Using search string

The following table describes an example of a search string and the output details:

Search string example

Output

BMC Helix Innovation Studio

Mid Tier

'abcd%'

Returns all characters that contain 'abcd%'. However, 'abcd' is not returned.

Returns all characters that contain 'abcd'.

'abcd_'

Returns all characters that contain 'abcd_'.

Returns all characters that contain 'abcd', but not 'abcd'.

Returns all characters that contain 'abcd' followed by at least one more letter after 'abcd'. For example, 'abcde', but not 'abcd'.

'abcd-'

Returns all characters that contain 'abcd-'.

Returns all characters that contain 'abcd-'.

'abcd['

Returns all characters that contain 'abcd['.

Important: In BMC Helix Innovation Studio, if you use MS SQL database and use the opening square bracket ([) wildcard symbol, you must escape the opening square bracket ([) wildcard symbol with a backslash (\). 


Returns all characters that contain 'abcd['.

Important: In Mid Tier, if you use MS SQL database and use the opening square bracket ([) wildcard symbol, you must escape the opening square bracket ([) wildcard symbol with a backslash (\).  However, for the PostgreSQL database, it does not honor the wildcard.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*