The “like” Criteria

In the example above, you may have been wondering about the percent sign in the first and second criteria.  There are two special characters used in conjunction with the “like” criteria.  These are the percent sign (%) and the underscore (_) and only have special significance in this type of criteria.

The “like” criteria allows you to partially match characters in alphanumeric fields.  When used in conjunction with these two special characters it offers a powerful selection method.

The “%” sign matches any number of characters regardless of what they are.  It also matches no characters at all.  The “_” character matches one and only one character, regardless of what it is.  Some examples will best show how to make use of these.

%but% matches “button”, “I would but I can’t”, and “but” but not “Butcher”

but% matches “button”, and “but” but not “I would but I can’t”, and “Butcher”

%base_ matches “based”, “Air Force bases”, but not “base”, “Air Force base”

To explain, in the first example “Butcher” does not match because of the capital “B” (remember that we set selection comparisons to be case sensitive).  In the second, “I would but I can’t” has characters before the “but” and again “Butcher” starts with a capital.  In the third, “base” and “Air Force base” do not match because the word “base” is not followed by a character.

In our example, the “Tech%” needed the “%” because some badges have the department entered as “Tech” and others as “Technical”.  The “P%” has the “%” because we wanted all names that began with a capital “P”.

If you are familiar with the DOS wild card characters “*” and “?” you will notice that “%” and “_” are very similar in function.  In fact the only difference is that the “%” works correctly even if in the middle of other characters.

Normally you will always use one or other of these special characters in the “like” criteria to get the most power from the command.