Filtering with Wildcards

The comparisons Is Like and Is Not Like have two characteristics which require more explanation. The first characteristic is that they let you build a custom list of allowable criteria. This is similar to the comparisons Is One Of and Is Not One Of described previously. As long as a field’s value matches any one of the values in the list, the record is printed.

The second unique characteristic is that you can use wildcard characters to create a search string. Wildcards let you create a group of matching data without being limited by a beginning and ending range. The strings are selected if they fit the pattern’s search criteria.

Search filters use two wildcard characters: ? and *. The ? allows any character to be in that position in the string. For example, let’s say that you want to print a report with all employees with the first name of Brian. You know that Brian is commonly spelled with a “y” as well as with an “i” and you want to find both spellings. The filter “Br?an” would match both spellings. The ? in the third position tells Crystal Reports that you don’t care which letter is used there as long as the rest of the string matches exactly.

Another example of using the ? wildcard is to generate a report showing records where there is a typo in the social security number. Sometimes, people mistakenly skip a number when typing in the SSN or they forget to include the dashes. Using a criteria of Is Not Like and setting the filter to ???-??-???? tells Crystal Reports which characters should have a number in them and which ones can only be the dash. By using the Is Not Like comparison, the report lists the records that have a typo in them because they don’t match the filter.

The second wildcard character is the *. It matches any number of characters in the string. This is best used when you want to find values that start a certain prefix. For example, if you want to find all the names that start with the letter C then you would use a filter string of C*.