Wildcards

The following table lists the wildcards that you can use with the LIKE operator in qualifications.

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. Note The close bracket (]) functions as a wildcard only when it is accompanied by an open bracket ([).	Microsoft SQL Server
[-]	Matches any single character within a specified range. Example: [a-f] matches the characters a, b, c, d, e, and f. Note The hyphen functions as a wildcard character only when preceded by an open bracket ([ or [^).	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 on Microsoft SQL Server and Sybase databases, enclose the character in brackets. For example, [_] matches only the _ character, not any single character. On Oracle, you cannot use the LIKE operator to search for literal percent (%) or underscore (_) characters.

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.

Validating wildcard pattern matching

A to Z in UPPER or lower case
Numbers from 0 to 9
Special characters _ (underscore) and - (dash)

Best practice

We recommend that you create an Active Link to generate an error message that informs the user than an invalid character was detected in the string for the field.

The active link would run 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).

If you need to use the bracket characters ( '[' or ']' ), you must follow the standard regular expression rules and use and escape character ('\') because brackets are used to define the syntax. For example, you would use ']' in order to use the close bracket character.

Wildcards

Validating wildcard pattern matching

Comments