Informix Error -219

-219 Wildcard matching may not be used with non-character types.

The WHERE clause in this statement includes a test of a noncharacter column using the LIKE or MATCHES keyword and the special characters that stand for multiple characters (for example, asterisk and question mark in MATCHES and percent and underscore with LIKE). Use these tests only with columns that are defined as CHAR or VARCHAR in the database. No automatic data conversion is provided. Check that the columns in the WHERE clause are as you intended. If so, the definition of the table has probably changed.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-219 is a clear, well-defined type restriction: LIKE or MATCHES (and their wildcard characters — %/_ for LIKE, */? for MATCHES) can only be used against CHAR/VARCHAR columns. There's no automatic data conversion, so applying either against a non-character column fails outright rather than implicitly casting.

Using LIKE/MATCHES against a column that isn't CHAR/VARCHAR — an INTEGER, DATE, DATETIME, or DECIMAL column, for example.
A schema change since the query was written — a column's type changed (from VARCHAR to INTEGER, for instance) breaking a previously-valid LIKE/MATCHES usage.
Generic or dynamically-built WHERE clauses applying LIKE-based search across many columns without checking their types first — a "search all columns" feature that fails the moment it reaches a non-character column.
Confusion about a column's actual type when writing ad hoc queries — assuming a column named "code" or "id" is textual when it's actually numeric.

Solutions / Resolution

Use LIKE/MATCHES only against CHAR/VARCHAR columns, per the official guidance — restructure the query if the intended comparison isn't actually against a character column.
If pattern-matching a non-character column is genuinely needed, explicitly cast or convert it to a character representation first, since no automatic conversion happens:
```
SELECT * FROM orders WHERE order_id::VARCHAR LIKE '100%';
```
Check whether the column's definition changed since the query was written, per the official guidance's own suggestion, if this appears unexpectedly on a query that used to work.
For generic or dynamic search features, check column types before applying LIKE-based filtering, and use appropriate exact-match or range comparisons for non-character columns instead.

Examples

LIKE against a numeric column

SELECT * FROM orders WHERE order_id LIKE '100%';
-- -219: order_id is INTEGER, not CHAR/VARCHAR

Fix — cast explicitly if pattern matching against the numeric value is genuinely needed:

SELECT * FROM orders WHERE order_id::VARCHAR LIKE '100%';

Or, more often, use a proper numeric comparison instead:

SELECT * FROM orders WHERE order_id BETWEEN 1000 AND 1099;

A schema change breaking a previously-valid query

-- Originally: status VARCHAR(20)
SELECT * FROM orders WHERE status LIKE 'ship%';
-- worked fine

-- After: status changed to a SMALLINT status-code column
-- Same query now fails with -219

Diagnostic Checks

Check the actual data type of the column in question:

SELECT c.colname, c.coltype FROM syscolumns c, systables t
WHERE t.tabid = c.tabid AND t.tabname = 'orders' AND c.colname = 'order_id';

Review recent schema changes (ALTER TABLE MODIFY) affecting that column's type, if this appeared unexpectedly.

Related Errors / Related Topics

-201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.
-217 — "Column column-name not found in any table in the query." Another common, everyday query-construction error in the same general category.

Confirm the column's actual data type before assuming the LIKE/MATCHES usage is correct — a recent schema change is a common, easy-to-overlook cause when a query that used to work suddenly fails.