MATCHES and LIKE in SQL conditions

Informix® supports MATCHES and LIKE in SQL statements. Netezza® 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 Netezza LIKE operator has no operator for [ ] brackets character ranges.

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

With Netezza , 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.

Solution

The database driver is able to translate Informix MATCHES expressions to LIKE expressions, when no [ ] bracket character ranges are used in the MATCHES operand.

However, for maximum portability, consider replacing the MATCHES expressions to LIKE expressions in all SQL statements of your programs.

Avoid using CHAR(N) types for variable length character data (such as name, address).

See also: MATCHES and LIKE operators.