Screening on Masked Fields
|
How to: |
|
Reference: |
A mask is an alphanumeric pattern that you supply for comparison to characters in a data field. The data field must have an alphanumeric format (A). You can use the LIKE and NOT LIKE or the IS and IS-NOT operators to perform screening on masked fields.
The wildcard characters for screening on masked fields with:
- LIKE and NOT LIKE operators are % and _. The percent allows any following sequence of zero or more characters. The underscore indicates that any character in that position is acceptable. The LIKE operator is supported in expressions that are used to derive temporary fields with either the DEFINE or COMPUTE command.
- IS (or EQ)
and IS-NOT (or NE) operators are $ and $*. The dollar sign indicates
that any character in that position is acceptable. The $* is shorthand
for writing a sequence of dollar signs to fill the end of the mask
without specifying a length. This combination can only be used at
the end of the mask.
In IF clauses and those WHERE clauses that can be translated into one or more IF clauses, you can treat the $ and $* characters as normal characters rather than wildcards by issuing the SET EQTEST=EXACT command.
Note: The IS (or EQ) and IS-NOT (or NE) operators support screening based on a mask for fixed length formats only. If the format is a variable length format, for example, AnV, use the LIKE or NOT LIKE operator to screen based on a mask.
Syntax: How to Screen Fields Based on a Mask (Using LIKE and NOT LIKE)
To search for records with the LIKE operator, use
WHERE field LIKE 'mask'
To reject records based on the mask value, use either
WHERE field NOT LIKE 'mask'
or
WHERE NOT field LIKE 'mask'
where:
- field
- Is any valid field name or alias.
- mask
- Is an alphanumeric or text character string you supply. There are two wildcard characters that you can use in the mask. The underscore (_) indicates that any character in that position is acceptable, and the percent sign (%) allows any following sequence of zero or more characters.
For related information, see Restrictions on Masking Characters.
Syntax: How to Screen Using LIKE and UNLIKE in an IF Phrase
To search for records with the LIKE operator, use
IF field LIKE 'mask1' [OR 'mask2'...]
To reject records based on the mask value, use
IF field UNLIKE 'mask1' [OR 'mask2' ...]
where:
- field
- Is any valid field name or alias.
- mask1, mask2
- Are the alphanumeric patterns you want to use for comparison. The single quotation marks are required if the mask contains blanks. There are two wildcard characters that you can use in a mask. The underscore (_) indicates that any character in that position is acceptable, and the percent sign (%) allows any following sequence of zero or more characters. Every other character in the mask accepts only itself in that position as a match to the pattern.
Syntax: How to Screen Fields Based on a Mask (Using IS and IS-NOT)
To search for records with the IS operator, use
{WHERE|IF} field {IS|EQ} 'mask'To reject records based on the mask value, use
{WHERE|IF} field {IS-NOT|NE} 'mask'where:
- field
- Is any valid field name or alias.
- IS|IS-NOT
- Are record selection operators. EQ is a synonym for IS. NE is a synonym for IS-NOT.
- mask
- Is an alphanumeric or text character string you supply. The wildcard characters that you can use in the mask are the dollar sign ($) and the combination $*. The dollar sign indicates that any character in that position is acceptable. The $* combination allows any sequence of zero or more characters. The $* is shorthand for writing a sequence of dollar signs to fill the end of the mask without specifying a specific length. This combination can only be used at the end of the mask.
For related information, see Restrictions on Masking Characters.
Reference: Restrictions on Masking Characters
- The wildcard characters dollar sign ($) and dollar sign with an asterisk ($*), which are used with IS operators, are treated as literals with LIKE operators.
- Masking with the characters $ and $* is not supported for compound WHERE phrases that use the AND or OR logical operators.
Example: Screening on Initial Characters
To list all employees who have taken basic-level courses, where every basic course begins with the word BASIC, issue the following request:
TABLE FILE EMPLOYEE PRINT COURSE_NAME COURSE_CODE BY LAST_NAME BY FIRST_NAME WHERE COURSE_NAME LIKE 'BASIC%' END
The output is:
Example: Screening on Characters Anywhere in a Field
If you want to see which employees have taken a FOCUS course, but you do not know where the word FOCUS appears in the title, bracket the word FOCUS with wildcards (which is equivalent to using the CONTAINS operator):
TABLE FILE EMPLOYEE PRINT COURSE_NAME COURSE_CODE BY LAST_NAME BY FIRST_NAME WHERE COURSE_NAME LIKE '%FOCUS%' END
The output is:
LAST_NAME FIRST_NAME COURSE_NAME COURSE_CODE --------- ---------- ----------- ----------- BLACKWOOD ROSEMARIE WHAT'S NEW IN FOCUS 202 JONES DIANE FOCUS INTERNALS 203
If you want to list all employees who have taken a 20x-series course, and you know that all of these courses have the same code except for the final character, issue the following request:
TABLE FILE EMPLOYEE PRINT COURSE_NAME COURSE_CODE BY LAST_NAME BY FIRST_NAME WHERE COURSE_CODE LIKE '20_' END
The output is:
LAST_NAME FIRST_NAME COURSE_NAME COURSE_CODE
--------- ---------- ----------- -----------
BLACKWOOD ROSEMARIE WHAT'S NEW IN FOCUS 202
JONES DIANE FOCUS INTERNALS 203
ADVANCED TECHNIQUES 201 Example: Screening on Initial Characters and Specific Length
The following example illustrates how to screen on initial characters and specify the length of the field value you are searching for. In this example, the WHERE phrase states that the last name must begin with BAN and be seven characters in length (the three initial characters BAN and the four placeholders, in this case, the dollar sign). The remaining characters in the field (positions 8 through 15) must be blank.
TABLE FILE EMPLOYEE PRINT LAST_NAME WHERE LAST_NAME IS 'BAN$$$$' END
The output is:
LAST_NAME --------- BANNING
Example: Screening on Records of Unspecified Length
To retrieve records with unspecified lengths, use the dollar sign followed by an asterisk ($*):
WHERE LAST_NAME IS 'BAN$*'
This phrase searches for last names that start with the letters BAN, regardless of the name length. The characters $* reduce typing, and enable you to define a screen mask without knowing the exact length of the field you wish to retrieve.
Syntax: How to Deactivate Wildcard Characters
SET EQTEST = {WILDCARD|EXACT}
where:
- WILDCARD
- Treats the $ and $* characters as wildcard characters. WILDCARD is the default value.
- EXACT
- Treats the $ and $* characters as normal characters, not wildcards, in IF tests and in WHERE tests that can be translated to IF tests.
Example: Selecting Records With SET EQTEST
The following request against the VIDEOTR2 data source creates two similar email addresses:
- handy$man@usa.com, which has a dollar sign.
- handyiman@usa.com, which has the letter i in the same position as the $ character in the other email address.
DEFINE FILE VIDEOTR2
SMAIL/A18= IF EMAIL EQ 'handyman@usa.com'
THEN 'handyiman@usa.com'
ELSE EMAIL;
SMAIL/A18 = STRREP(18,SMAIL,1,'_',1,'$',18,SMAIL);
END
TABLE FILE VIDEOTR2
PRINT SMAIL
BY LASTNAME BY FIRSTNAME
WHERE SMAIL EQ 'handy$man@usa.com'
ON TABLE SET EQTEST WILDCARD
END
With SET EQTEST=WILDCARD (the default), the WHERE test WHERE SMAIL IS 'handy$man@usa.com' returns both the record with the $ in the address and the record with the letter i in the address because the $ is treated as a wildcard character, and any character in that position causes the record to pass the screening test:
LASTNAME FIRSTNAME SMAIL
-------- --------- -----
HANDLER EVAN handy$man@usa.com
handyiman@usa.com
Changing the ON TABLE SET command to ON TABLE SET EQTEST EXACT returns just the ONE email address with the $ character because the dollar sign is now treated as a normal character and only passes the test if there is an exact match:
LASTNAME FIRSTNAME SMAIL -------- --------- ----- HANDLER EVAN handy$man@usa.com