TEXT

The TEXT data type stores any kind of text data. It can contain both single-byte and multibyte characters that the locale supports. The term simple large object refers to the TEXT and BYTE data types.

A TEXT column has a theoretical limit of 2³¹ bytes (two gigabytes) and a practical limit that your available disk storage determines.

No more than 195 columns of the same table can be declared as TEXT data types. (The same restriction also applies to BYTE data types.)

You can store, retrieve, update, or delete the values in a TEXT column. You cannot, however, use TEXT operands in arithmetic or string expressions, nor can you assign literals to TEXT columns in the SET clause of the UPDATE statement. You also cannot use TEXT values in any of the following ways:

• With aggregate functions
• With the IN clause
• With the MATCHES or LIKE clauses
• With the GROUP BY clause
• With the ORDER BY clause

You can use TEXT operands in Boolean expressions only when you are testing for NULL values with the IS NULL or IS NOT NULL operators.

You can insert data into TEXT columns in the following ways:

• With the dbload or onload utilities
• With the LOAD statement (DB–Access)
• From TEXT host variables (ESQL)

You cannot use a quoted text string, number, or any other actual value to insert or update TEXT columns.

When you select a TEXT column, you can choose to receive all or part of it. To retrieve it all, use the regular syntax for selecting a column. You can also select any part of a TEXT column by using subscripts, as this example shows:

SELECT cat_descr [1,75] FROM catalog WHERE catalog_num = 10001

This statement reads the first 75 bytes of the cat_descr column associated with the catalog_num value 10001.

A built-in cast exists to convert TEXT objects to CLOB objects. For more information, see the IBM Informix: Database Design and Implementation Guide.

Strings of the TEXT data type are collated in code-set order. For more information on collating orders, see the IBM Informix: GLS User's Guide.

Nonprintable Characters in TEXT Values

TEXT columns typically store documents, program source files, and so on. In the default U.S. English locale, data objects of type TEXT can contain a combination of printable ASCII characters and the following control characters:

• Tab (CTRL-I)
• New line (CTRL-J)
• New page (CTRL-L)

Both printable and nonprintable characters can be inserted in text columns. IBM Informix products do not do any checking of data values that are inserted in a column of the TEXT data type. (Applications may have difficulty, however, in displaying TEXT values that include non-printable characters.) For detailed information on entering and displaying nonprintable characters, refer to Nonprintable Characters with CHAR.