Oninit
Talk to an expert →

Informix Error -29040: Cannot translate MATCHES pattern to LIKE pattern.

Cause and resolution

Cannot translate MATCHES pattern to LIKE pattern.

The Gateway does not support and cannot translate one or more characters in the MATCHES condition of your SQL statement. Try to rewrite the statement to use LIKE instead of MATCHES condition syntax or to not include MATCHES wildcard characters such as [ ] and ? or *. Check also that you did not try to include a non-CHAR or VARCHAR column with MATCHES in the WHERE clause.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-29040 fires when the Gateway can't translate a MATCHES condition's pattern into an equivalent LIKE pattern — per the official guidance, the target DBMS doesn't have a MATCHES equivalent, so the Gateway must translate to LIKE.

  1. A MATCHES pattern using a character the Gateway can't translate, per the official guidance — the direct, only cause.
  2. MATCHES applied to a non-CHAR/VARCHAR column, per the official guidance — a further named case.
Solutions / Resolution
  1. Rewrite the statement using LIKE instead of MATCHES, per the official guidance, or
  2. Avoid MATCHES wildcard characters ([, ], ?, *) the Gateway can't translate, per the official guidance.
  3. Confirm MATCHES is applied only to CHAR/VARCHAR columns, per the official guidance.
Examples
A MATCHES pattern the Gateway can't translate
SELECT * FROM orders WHERE status MATCHES '[AB]*';
-- -29040: Gateway can't translate this MATCHES pattern to LIKE
Corrected — using LIKE directly
SELECT * FROM orders WHERE status LIKE 'A%' OR status LIKE 'B%';
Diagnostic Checks
  1. Check the MATCHES pattern for untranslatable wildcards, and the target column's type.
Related Errors / Related Topics

No closely related error codes are cross-referenced for -29040 in this set yet.

A MATCHES pattern couldn't be translated to LIKE for the target DBMS — rewrite using LIKE directly, or avoid untranslatable wildcards.