Selecting Records Without Wildcard Characters

How to:

An IF or WHERE clause in a request selects records based on whether they satisfy the criteria specified in an expression. However, an IF or WHERE clause using the EQ (is equal to) or IS operator treats the dollar sign character ($) as a wildcard character, meaning that any character in that position satisfies the criteria. Also, the dollar sign followed by an asterisk ($*) is a wildcard combination, meaning that any combination of characters satisfies the criteria. By default, therefore, it is impossible to use this syntax to select only those records that contain a $ character or a $* character combination.

The expression in an IF clause is more restrictive than the expression in a WHERE clause. An IF expression must compare a field value to one or more alphanumeric literals. A WHERE expression can compare two fields and can contain compound expressions created using the logical operators AND, OR, and NOT. Depending on the complexity of the expression, the WHERE clause can be broken down into one or more IF clauses.

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.
Top of page
x
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 e-mail addresses:

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 e-mail 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
Information Builders