MATCHES and LIKE in SQL conditions

Informix supports MATCHES and LIKE in SQL statements, while ORACLE supports the LIKE statement only.

MATCHES allows you to use brackets to specify a set of matching characters at a given position:

( col MATCHES '[Pp]aris' ).
( col MATCHES '[0-9][a-z]*' ).

In this case, the LIKE statement has no equivalent feature.

The following substitutions must be done to convert a MATCHES condition to a LIKE condition:

• MATCHES keyword must be replaced by LIKE.
• All '*' characters must be replaced by '%'.
• All '?' characters must be replaced by '_'.
• Remove all brackets expressions.

Solution

SQL statements using MATCHES expressions must be reviewed in order to use LIKE expressions.

ORACLE provides the TRANSLATE function which can be used to replace MATCHES in specific cases. The TRANSLATE function replaces all occurrences of characters listed in a 'from' set, with the corresponding character defined in a 'to' set.

Informix: WHERE col MATCHES'[0-9][0-9][0-9]'
ORACLE: WHERE TRANSLATE(col,'0123456789','9999999999')='999'

출처 : IBM Informix Genero Business Development Language User Guide, v2.41 (SC27-3852-01)