| SQL programming / SQL portability | |
Use the standard LIKE operator instead of the MATCHES operator.
The MATCHES operator allows you to scan a string expression:
SELECT * FROM customer WHERE customer_name MATCHES "A*[0-9]"
Here is a table listing the database servers which support the MATCHES operator:
| Database Server Type | Support for SQL MATCHES operator? |
|---|---|
| IBM® DB2® UDB | No |
| IBM Informix® (1) | Yes |
| Microsoft™ SQL Server (2) | No |
| MySQL | No |
| Oracle Database Server | No |
| PostgreSQL | No |
| Sybase ASE | No |
| SQLite | No |
The MATCHES operator is specific to IBM Informix SQL. The equivalent standard operator is LIKE. For maximum portability, replace MATCHES expressions in your SQL statements with a standard LIKE expression. MATCHES uses * and ? as wildcards. The equivalent wildcards in the LIKE operator are % and _. Character ranges [a-z] are not supported by the LIKE operator.
Note that the Genero language includes a MATCHES operator. For example, in expressions such as: IF custname MATCHES "S*". Do not confuse the language MATCHES operator with the SQL MATCHES operator, used in SQL statements. There is no problem in using the MATCHES operator of the language.
A program variable can be used as parameter for the MATCHES or LIKE operator, but you must pay attention to blank padding semantics of the target database. 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.
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.