MATCHES and LIKE operators

Use the standard LIKE operator instead of the MATCHES operator.

The MATCHES operator is specific to IBM® Informix® SQL, at allows to compare a character string column to a search pattern:

SELECT * FROM customer WHERE customer_name MATCHES "A*"

Note: The Genero language supports a MATCHES operator. Do not confuse the language MATCHES operator (used in BDL instructions such as IF custname MATCHES "S*"), with the SQL MATCHES operator (used in SQL statements). There is no problem in using the MATCHES operator of BDL.

The standard SQL operator for pattern search is LIKE:

SELECT * FROM customer WHERE customer_name LIKE "A%"

When using a non-Informix driver, the MATCHES expressions using a string constant are replaced by a LIKE expression.

Important: Only MATCHES expressions with a string constant can be converted to LIKE expressions, if the MATCHES uses a ? SQL parameter place holder, no translation is done.

Table 1. SQL MATCHES operator support by database brand
Database Server Type	SQL MATCHES support
IBM DB2® LUW	Emulated, see details
IBM Informix	Yes, native SQL feature
IBM Netezza	Emulated, see details
Microsoft™ SQL Server	Emulated, see details
Oracle® MySQL	Emulated, see details
Oracle Database Server	Emulated, see details
PostgreSQL	Emulated, see details
SAP® ASE	Emulated, see details
SAP HANA®	Emulated, see details
SQLite	Emulated, see details

For maximum portability, replace SQL MATCHES expressions by LIKE expression. MATCHES uses * and ? as wildcards. The equivalent wildcards in the LIKE operator are % and _.

Important: MATCHES character ranges such as [a-z] cannot be converted for the LIKE operator.

Pay attention to blank padding semantics of the target database when using a program variable (or ? SQL parameter place holder) after the LIKE operator: If the program variable is defined as a CHAR(N), it is filled by the runtime system with trailing blanks, in order to have a size of N. For example, when a CHAR(10) variable is assigned with "ABC%", it contains actually "ABC% " (with 6 additional blanks). If this variable is used in a LIKE expression in an SQL statement, the database server will search for column values matching "ABC"+ some characters + 6 blanks. To avoid automatic blanks, use a VARCHAR(N) data type instead of CHAR(N) to hold LIKE patterns.

Pay also attention to database specific semantics of the LIKE operation, especially when using CHAR(N) data types. For example, with Oracle DB, the expression custname LIKE '%h', if custname is defined as CHAR(30), Oracle will only find the rows when the custname values end with a 'h' at the last character position (30), values such as 'Smith' will not be found. Similarly, when doing custname LIKE 'ab_', rows where the column type is CHAR(N>3), with values such as 'abc' will not match in Oracle, IBM DB2 and PostgreSQL, because of the significant trailing blanks.

PostgreSQL provides the SIMILAR TO operator, allowing [start-end] character range specification as in MATCHES.

As a general advice, use the VARCHAR type for variable string data, and leave CHAR usage for fixed-length character string data such as codes.