| SQL adaptation guide For SQLite 3.5.x, 3.6.x / Data manipulation | |
Informix® supports MATCHES and LIKE in SQL statements, while SQLite 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 SQLite LIKE operator has no operator for [ ] brackets character ranges.
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.