Search Condition Using MATCHES

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
     FROM    REPORT
     WHERE   AUTHOR  MATCHES  'San%' ;

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 the field.

MATCHES can make use of a regular index on the field. It will not use a Metamorph index.