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 / MariadDB	Emulated, see details
Oracle Database Server	Emulated, see details
PostgreSQL	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 in static SQL or a ? 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, and the actual pattern for the LIKE operator will contain trailing blanks. For example, when a CHAR(10) variable is assigned with "ABC%", it will in fact contain "ABC% " (with 6 additional blanks). When used as SQL parameter for a LIKE expression, the database server will search for column values matching "ABC"+ some characters + 6 blanks. To avoid this, use a VARCHAR(N) data type instead of CHAR(N) to hold LIKE patterns.

Some database engines have specific semantics for the LIKE operator, 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 match. Similarly, when doing custname LIKE 'ab_', rows where the column type is CHAR(N>3), values such as 'abc' will not match in Oracle, IBM DB2 and PostgreSQL, because of the significant trailing blanks.

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.

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