Wildcards and operators for searching

You can use special characters to search when you do not know the entire value that you want to search for or when you want to specify the type of information that you are searching for. The type of value that is used in a field, such as a numeric or string value, determines which operators can be used to filter for values in the field. If you want to change how values are searched for in a specific field, you can edit the SQL where clause for a field.

Rules for Wildcards and Operators

The following rules apply to fields that contain numbers, such as the Priority field or the Work Order Number field:

By default, commas can be used only to separate numbers that have more than three digits. To use commas as separators for multiple numeric values, in the System Properties application, set the mxe.system.IntegerQBEsearchCommaAsOr property to 1. If the property is set to 1, commas can be used only as separators for whole numeric values. For example, 1,245 returns records that contain a value of 1 and records with a value of 245.
You cannot search for a range of numeric values. To search for more than one value, you must separate each individual value by using a comma, use an operator, or use a wildcard.

The following rules apply to fields that contain dates, such as the Target Delivery Date field or the Shipped Date field:

If you specify a date without a time, the records that are returned have values for times during the date.
If you specify a date with a time and specify the minute, the records that are returned have values for the 12 hours after the date and time or until the end of the day.
If you specify a date with a time and do not specify a minute, the records that are returned have values from the specified time until the end of the day.
You cannot specify a range of dates, and you cannot specify more than one date in a field.
You can use the TODAY and YTD (Year to Date) keywords to query for information. YTD lists all records with dates for the present year. TODAY lists all records with the present date. You can use operators with the TODAY keyword to determine what times from the present day are shown.

The following rules apply to fields that contain strings, such as the Name field and the Type field:

Searches are case sensitive.
If the equal sign (=) is not used, wildcards are added to the beginning and end of the string so that any record that includes the string in the field is found. However, if you add a wildcard to a string, additional wildcards are not automatically added.
You can use commas to search for more than one term. For example, you could search for rails, generators to find all records that have rails or generators in that field.

The WHERE Clause

You can edit the WHERE clause to change how values are searched for in a specific field. To edit the WHERE clause, add a value to a field and then select Advanced Search > Where Clause. If you are searching for null values by editing the WHERE clause, you use the SQL comparison values IS NULL and IS NOT NULL to select and compare null values.

Wildcards

A wildcard character is used to search for a partial value in a field when you do not know the entire value. You place the wildcard where the unknown characters occur. You can use more than one wildcard character in a single search.

Wildcard characters

Wildcard	Use
* or %	Substitute for a string of characters. For example, 123* or 123% find records that start with 123, such as 123, 12345, and 123ABC.
? or _	Substitutes for a single character. For example, 123? or 123_ find any four-character records that start with 123, such as 1234 or 1239.

Operators

An operator is used to specify the type of information that you are searching for. You can only use operators in fields that have a search type of text, such as Description fields.

Operator characters

Operator	Use
ampersand (&)	Find records that contain at least one occurrence of all of your query terms. For example, `Rail&Line` returns records that include both `Rail` and `Line`.
hyphen (-)	Find records that contain at least one query term in a set of two or more, and prioritize the results based on the order of terms with the operator. For example, `Rail-Line` returns records that only include `Rail`, records that include only `Line`, and records that include both, but the records that include only `Rail` are ranked higher on the results page.
pipe (\|)	Find records that contain at least one occurrence of any of your query terms. For example, `Rail\|Line` returns records that include only `Rail`, records that include only `Line`, and records that include both.
tilde (~)	Find records that contain one query term and not another. For example, `Rail~Line` returns records that include `Rail`, but does not return records that include `Line`.
equals (=)	Find records that are an exact match to your query. For example, filtering records with a status of `APPR` also returns records that have a status of `WAPPR` unless you type `=APPR` in the status field. If you are searching for a set of exact values, separate the values with commas. For example, filtering records with priorities =1, =2, and =3 returns records that have a 1, 2, or 3 priority.
~null~	Find records that do not include a value in a specific field. When using the List tab, the Advanced Search action, or the More Search Fields dialog box to search for records that contain null values, use the following syntax: To search for records that contain a null value for a field, enter `~null~` To search for records that do not contain a null value for a field, enter `!=~null~` You cannot use the `~null~` or `!=~null~` operator in fields that have date values.
<	Find values that are less than the value that is in the field. For example, <3 returns records with values that are less than 3, such as 2 or 1. You cannot use the less than symbol (<) for fields that have string values.
!=	Find all values except for the value that is in the field. For example, !=7 returns records with values such as 4, 5, 6, and 8. You cannot use the not equal operator (!=) for fields that have date values.
>=	Find values that are greater than or equal to the value that is in the field. For example, >=7 returns records with values such as 4, 5, 6, 7, and 8. You cannot use the greater than or equal to symbol (>=) operator for fields that have string values.
<=	Find values that are less than or equal to the value that is in the field. For example, <=3 returns records with values such as 3, 2, or 1. You cannot use the less than or equal to symbol (<=) for fields that have string values.
>	Find values that are greater than the value that is in the field. For example, >3 finds records with values that are greater than 3, such as 4, 5, or 6. You cannot use the greater than symbol (<) for fields that have string values.