MATCHES and LIKE in SQL conditions

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

The MATCHES operator of Informix uses the star (*), question mark (?) and square braces ([ ]) wildcard characters. The LIKE operator of SQL SERVER offers the percent (%), underscore (_) and square braces ([ ]) wildcard characters:

( col MATCHES 'Smi*' AND col NOT MATCHES 'R?x[a-z]' )
( col LIKE 'Smi%' AND col NOT LIKE 'R_x[a-z]' )

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).