MATCHES and LIKE in SQL conditions

Informix^® supports MATCHES and LIKE in SQL statements. PostgreSQL supports the LIKE statement as in Informix, plus the ~ operators that are similar but different from the Informix MATCHES operator.

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 brackets to specify a set of matching characters at a given position:

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

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

The PostgreSQL ~ operator expects regular expressions as follows: ( col ~ 'a.*' )

With PostgreSQL, columns defined as CHAR(N) are blank padded, and trailing blanks are 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.