MATCHES and LIKE in SQL conditions

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

MATCHES requires * and ? wildcard characters, and LIKE uses the % and _ wildcards was equivalents.

( col MATCHES 'Smi*' AND col NOT MATCHES 'R?x' )
( col LIKE 'Smi%' AND col NOT LIKE 'R_x' )

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]*' )

The LIKE operator has no operator for [ ] brackets character ranges.

With ORACLE, columns defined as CHAR(N) are blank padded, and trailing blanks as significant in the LIKE expressions. As result, with a CHAR(5) value such as 'abc ' (with 2 trailing blanks), the expression (colname LIKE 'ab_') will not match. To workaround this behavior, you can do (RTRIM(colname) LIKE 'pattern'). However, consider adding the condition AND (colname LIKE 'patten%') to force the DB server to optimize the query of the column is indexed. The CONSTRUCT instruction uses this technique when the entered criteria does not end with a * star wildcard.