Reporting Language Primer > Screening Alphanumeric Content

Screening Alphanumeric Content

Topics:

Screening Masked Fields: LIKE

There are three (3) relational operators designed to screen only alphanumeric data. These are CONTAINS, OMITS, and LIKE (NOT LIKE is the opposite of LIKE).

CONTAINS is useful when an alphanumeric field may have the same string of characters appearing in differing places in multiple records. For example, suppose you are interested in seeing which employees live on Lombardo Avenue.

Issue the following request:

TABLE FILE EMPLOYEE
PRINT ADDRESS_LN1
BY FIRST_NAME BY LAST_NAME
WHERE ADDRESS_LN1 CONTAINS 'LOMBARDO';
END

Run the request. The output is:

The literal LOMBARDO is the string of characters searched for in each record. This example retrieves all the records in which that string appears, regardless of where in the ADDRESS_LN1 field the value appears.

Of course, this example would also retrieve records, if there were any, for employees who live on Lombardo Street, or Lombardo Boulevard. To limit the records to those living on Lombardo Avenue, you would use the following request. Since we know there is only one record containing "Lombardo", do not issue this request:

TABLE FILE EMPLOYEE
PRINT ADDRESS_LN1
BY FIRST_NAME BY LAST_NAME
WHERE ADDRESS_LN1 CONTAINS 'LOMBARDO' AND ADDRESS_LN1 CONTAINS 'AVE';
END

By using two conditions connected by AND, you avoid problems related to the positions of the words "Lombardo" and "Avenue" relative to each other (for instance, "LombardoAve" or "Lombardo Avenue"), and problems related to abbreviations of the word "Avenue".

OMITS is the opposite of CONTAINS. You exclude records that contain the character string you specify. In this next request, the field TYPE, which defines the type of addresses available for employees, is used to exclude employee bank addresses.

Issue the following request:

TABLE FILE EMPLOYEE
PRINT ADDRESS_LN1 BY FIRST_NAME BY LAST_NAME BY TYPE
WHERE TYPE OMITS 'BANK';
END

Run the request. The output is:

Screening Masked Fields: LIKE

Another type of screening that uses only alphanumeric data is the type that uses a mask to identify data. A mask is an alphanumeric pattern you supply that is used to compare to characters in a data field value. The relational operator you use to screen a masked field is LIKE.

The format of a screening condition using LIKE is:

WHERE field LIKE 'mask';

The field in the above structure is the field in which the mask appears. The mask is an alphanumeric string of characters enclosed in single quotation marks ('). To retrieve records that do not contain the specified mask, use NOT LIKE. When you use a mask, you use two masking characters:

The percent sign (%) to indicate any number of characters, which may be zero (0).
The underscore (_) to indicate a single character in a specific position.

Here is a simple example of how you might use the percent sign (%) in a mask. Assume you want to retrieve every employee whose last name begins with a certain letter, B for instance. Although each last name may be different, you can still retrieve each one because the percent sign (%) matches any arbitrary sequence of characters that occurs starting in the position you place it.

Issue the following request:

TABLE FILE EMPLOYEE
PRINT LAST_NAME
WHERE LAST_NAME LIKE 'B%';
END

Run the request. The output is:

You use the underscore (_) to specify a position in the record and to indicate that any character in that position is acceptable. As a contrast, when you use CONTAINS, you specify a string that can occur anywhere in a record.

The following example shows how you can retrieve all employees who are managers. Each employee is assigned a three-character job code. The second character in this code indicates whether or not a job is on the management level. If the second character is a 1, it is, otherwise, it is not. In this following example, you use the underscore (_) to retrieve only job codes whose second character is 1, with any character in the first and third position.

Issue the following request:

TABLE FILE EMPLOYEE
PRINT JOBCODE
BY LAST_NAME BY FIRST_NAME
WHERE JOBCODE LIKE '_1_';
END

Run the request. The output is:

Only employees whose job codes match the mask you provided are retrieved.

	WebFOCUS

Feedback