The MATCHES keyword allows you to match fields against expressions. This is most useful when you have fields with a small amount of text and do not need the full power of Metamorph. Typical uses would be names, part numbers or addresses.
In the query an underscore will match any single character, and a percent sign will match any number of characters. For example
SELECT AUTHOR, DEPT
WHERE AUTHOR MATCHES 'San%' ;
The result will be:
AUTHOR DEPT Sanchez, Carla MKT Sanders, George G. FIN
The special characters used with MATCHES can be changed using the
set matchmode SQL statement. The default value of 0 produces
the behavior documented above which is standard in SQL. Setting
MATCHMODE to 1 will change the special characters such that
asterix will match any number of characters, and a question mark will
match any single character, which is more familiar to many people.
Comparing the results to the earlier example using
LIKE you will see that
Claus, Santa does not match, as the match has to occur at the beginning of
MATCHES can make use of a regular index on the field. It will not use a Metamorph index.