Using Wildcards and Operators

About Wildcards and Operators

Wildcards, such as the asterisk (*) and the question mark (?), and logical operators, such as AND (&) and OR (|), are used extensively in Standard Search.  These operators or wildcards narrow the search, decreasing search time,   When a database contains a large amount of data, a global search with no specific conditions can slow the system, resulting in a long search time, and many unnecessary results.  Therefore, wildcards and logical operators can help you save time and improve your search results.

In the Standard Search feature, you can enter a value or a portion of the value for a property using wildcards.  These wildcard characters are valid for Standard Search:

  • Question Mark (?) - Use this to represent one character.  For example, if you know that an item number starts with "AB" and ends with a single variable character; ABC, ABB, and ABD, then all items that start with AB and end on one single character are displayed.
  • Asterisk (*) - Use this to represent one or more characters.  For example, if you know that an item number starts with AB, you would enter AB*.  Therefore, all items that start with AB are displayed.

Searches can be refined using relational operators such as AND (&), OR (|), and NOT (!).  These operators allow you to search for multiple values for the same property, or to specify what not to include in a search.  These are some of the relational operators that are valid for Standard Search.:

  • | (the pipe - relational OR operator) - use this to have CES search for one term or another.  For example, if you know that an item number starts with AB or BA, enter AB*|BA*.  All item numbers that start with AB or BA are displayed.
  • ! (The exclamation point - relational operator NOT) - use this to search for an item that does not contain one or more characters.  For example, if you know that an item number does not start with A, enter !A*.  All item numbers that start with letters other than A are displayed>
  • & (the ampersand - relational operator AND) - use this to search for items that contain two or more groups of characters.  For example, if you know that an item number starts with A, but also contains 123, enter A*&*123*.  All item numbers that start with A and contain 123 are displayed.

This table provides examples, and details the use of wildcards and operators:

To search for... Enter...
An item AB123 AB123
An item number that starts with "AB", for example, ABC or AB123 AB*
An item number that ends with "2345", for example, 12345 and ABC12345 *2345
An item number that contains "AB" anywhere in the string *AB*
An item number that starts with "H" or "I" H* | I*
An item number starts with "AB" and ends with a single variable character, for example, ABC, ABB, and ABD AB?
An item number ends with "BC" and starts with a single variable character, for example ABC, BBC, and CBC ?BC
An item number that starts with "A: and ends with "C" and contains a single variable character, for example ABC, AAC or ACC A?C
An item number starting with "A", and containing "123" A* & *123*
An item number starts with "A" and ends with two variable characters, for example AAC, ABC or ACC. A??
An item number that ends in "C" and beings with two variable characters, for example, ABC, ACC, or ADC ??C
An item number that contains "B" and begins and ends with a variable character, for example ABC, CBA, ABB, or BBA ?B?
  • Enter asterisk (*) to represent one or more characters: If the you knows that an item number starts with AB, enter AB*. All items results start with AB are displayed.
  • Enter question mark (?) to represent one character: If you know that an item number starts with AB, but do not know the last character, enter AB?.
  • Enter pipe (|) to have CES search for one term or another: If you know that an item number starts with AB or BA, enter AB*|BA*. All item numbers that start with AB or BA are displayed.
  • Enter exclamation mark (!) to search for an item that does not contain one or more characters: If you know that an item number does not start with A, enter !A*. All item numbers that start with letters other than A are displayed.
  • Enter Ampersand (&) to search for items that contains two or more groups of characters: If you know that an item number starts with A, but also contains 123, enter A*&*123*.  All item numbers that start with A and contain 123 are displayed.

This table provides relational operators valid for internal searches. Further, you can use and combine wildcards, and relational operators in the procedures mentioned in Standard Search.

Operator Definition Example
> Greater than >20
>= Greater than or equal to >=20
< Lesser than <20
<= Less than or equal to <=20
= Equal =20
!= Not Equal !=20
: Inclusive range 20:30
! NOT !20:30
& AND >30 & <35
| OR 30|40
() Groupings (20:30) | (30:40)
@ NULL @
!@ NOT NULL !@

When using Wildcards and Operators

  • You can search on date properties using most of the same operators you use for number searches. When you enter a date, you need to use the exact date format that is specified. For example, if you are using the date format DD-MM-YY, you would need to enter the full date to get the search results.
  • Specify a wildcard attribute at the end of the search string if the date format includes the time. Therefore, if you want to search all items with a date field that match march 4, 2001, and are using the date format, you should enter 04-Mar-01* to obtain the correct search results.
  • You can use operators such as, greater than (>) and, less than (<) to specify a range of dates. For example, to search for all items that fall between February 1, 2001, and February 28, 2001, you will enter >01-FEB-01 & 28-FEB-01.
  • The Range operator (colon) is not supported (:).
  • Dates are not case-sensitive.

Special Characters

You can include special characters, for example asterisk (*), and pipe (|) in the search criteria. However, these types of characters require you to type a back slash (\) before the special characters or enclose the entire string containing them in double quotes (") to clarify their use.

This table lists all the characters that require special handling in CES searches:

Character Definition
< Less than
> Greater Than
[ Open bracket
] Close bracket
& Ampersand
: Colon
| Pipe
! Exclamation mark
* Asterisk
, Comma
\ Black slash
" Double quotes
( Open parentheses
) Close parentheses
@ At
? Question mark
= Equal sign

This table lists search examples using special characters:

To search for... Enter...
A Company named "Flint & Company" "Flint & Company"
An item number "505[000]" 505\[000\]

Date Searches

  • You can search on date properties using most of the same operators you use for number searches. When you enter a date, you need to use the exact date format that is specified. For example, if you are using the date format DD-MM-YY, you would need to enter the full date to get the search results.
  • You also need to specify a wildcard attribute at the end of the search string if the date format includes the time. Therefore, if you want to search all items with a date field that matched March 4, 2001, and are using the date format mentioned earlier, you would enter 04-Mar-01* to get the correct results.
  • You can also use operators such as, greater than (>) and, less than (<) to specify a range of dates. In this instance, you do not need to enter wildcards since you are not looking for an exact match. For example, to search for all items that fall between February 1, 2001, and February 28, 2001, you will enter >01-FEB-01 & <28-FEB-01. The only operator you cannot use for this type of search is the range (:) operator.
  • Dates are not case-sensitive.

Accessing the Advanced Access: Standard Search Window

Within CES, there are many methods of accessing the Advanced Access: Standard Search window. To complete the scenario below, select one of the following methods to access the Advanced Access: Standard Search window:

 
If You Are Using the... Then...

Advanced Access

  1. In the Main Access Area, click the Advanced Access link

  2. In the Advanced Access window, click the Standard Search tab to access the Standard Search window.

Favorites
  1. From the Favorites pane, expand My Searches to display your list of saved searches.

  2. Select the saved search you wish to view, right-click, and select Open from the shortcut menu.

How To use Wildcards and Operators

This task explains how to obtain search results using wildcards and operators:
  1. In the Main Access Area, click the Advanced Access link. The Advanced Access page appears.

  2. Click the Standard Search tab. The search attributes in the Standard Search tab appear.

  3. From the Environment list, select the attribute for which you want to search, either Company or Item.  In this scenario,  Item has been selected.  The Type list is refreshed automatically based on the selection made in the Environment list.

  4. In the Search Criteria area, define the criteria for the attributes of which you will be searching. The search attributes are mapped to the criteria you specify. You can specify the search criteria in one or multiple fields to get the desired search results. For example, the Manufacturer Item Number starts with numeric 1. For this manufacturer item number, you can also have multiple manufacturers. To narrow the search and to get the desired search results,  you can specify the criteria of 1* for manufacturer item number, followed by the manufacturer or manufacturers in the Manufacturer field. You can also click the Query Builder/Valid Values button to further define your search. For more details, see Using Valid Values, and Using Query Builder. The methodology is same when you are searching for companies.

  5. Once you have completed your search criteria, click the Search button to conduct the search.  The Search Results window appears with the items that meet the defined criteria.

This table describes the buttons displayed in the Advanced Access page:

The Advanced Access: Standard Search Window

This table describes the Advanced Access: Standard Search window:

This table describes the fields that exist within the Advanced Access: Standard Search window:

Fields Description
Search Form A configuration on a class which has a set of search and results properties.
Manufacturer Item No. A item from a manufacturer, usually defined by a unique manufacturer identifier number.
Manufacturer An external organization that manufactures items.
Supplier Item No. An item from a supplier, usually defined by a unique supplier identifier number that may be related to more than one manufacturer item number.
Supplier An external organization that supplies items.
Internal Item No. An item used (consumed) internally by an internal organization.  This item may be used to construct Bills of Materials (BOMs) managed by the internal organization.
Internal Organization Usually an organization that belongs to the hierarchy of organizations.
Mfr Item Description A unique name identifier of the manufacturing item.
Supplier Item Description A unique name identifier of the supplier item.
List Price Displays all the list/fixed prices associated with the selected item.
Currency Something that is in circulation (coins, treasury notes, and bank notes) as a medium of exchange.
List Price UOM How the price is calculated in bulk quantity.
Internal Item Description A unique name identifier of the internal item.

This table describes the buttons that appear in the Advanced Access: Standard Search window.

Button Description
Conducts the search based on the criteria entered.
Clears all the entered criteria in the Advanced Access window.
Saves the search criteria and places it in Favorites for future access.
Applies a configuration to the search criteria and the concluding search results.
Expands the search criteria to include additional attributes for defining the search.