Wildcards
The following table lists the wildcards that you can use with the LIKE operator in qualifications.
Wildcards
Wildcard | Description | Supported database |
%|
Matches any string of 0 or more characters. Example: J%son matches Jackson, Johnson, Jason, and Json. To include leading and trailing characters, you must use the % symbol. For example, to match Jill Bobbington, Bobby Fenton, Bob Compton, and Bob Stone in the Submitter field, enter:'Submitter' LIKE "%Bob%ton%"
|
- Oracle
- Microsoft SQL Server
_|
(Underscore character) Matches any single character. Example: B_b matches Bab, Bob, and Bub.
|
- Microsoft SQL Server
- Sybase
[ ]|
Matches any single character within a specified set. Example: [abcf] matches the characters a, b, c, and f.
|
- Microsoft SQL Server
[-]|
Matches any single character within a specified range. Example: [a-f] matches the characters a, b, c, d, e, and f.
|
- Microsoft SQL Server
[^]|
Matches any single character not within the specified set or range. Example: [^abcf] matches all characters except a, b, c, and f.[^a-f] matches all characters except the characters a, b, c, d, e, and f.
|
- Microsoft SQL Server
Wildcard symbols are interpreted as wildcards only when used with the LIKE operator; otherwise, they are interpreted literally.
To use percent (%), underscore (_), or open bracket ([) characters as literal text in a LIKE operation:
- Microsoft SQL Server and Sybase — Enclose the character in brackets. For example, [_] matches only the _ character, not any single character.
Validating wildcard pattern matching
When checking input fields for valid characters, check the field for anything other than what is acceptable. For example, the following is the list of acceptable input characters, so if any other character is entered an error message should be sent to the user.
- A to Z in UPPER or lower case
- Numbers from 0 to 9
- Special characters _ (underscore) and - (dash)
The Active Link would fire on Submit, Modify, and Lose Focus actions for the character field being used. For this example the character field is called 'Input'. The Run If qualification would be:
'Input' LIKE "%[^a-zA-Z0-9_-]%"
The If Action would contain a Message action (Type: Error).