Text Expressions

A text expression evaluates to data with the TEXT, NTEXT, or ID data type. Text expressions can be any combination of the following:

• Text literals; for example, 'BOSTON' or 'Current Sales Report'
• Text dimensions; for example, district or month
• Text variables or formulas; for example, product.name
• Functions that yield text results; for example, JOINLINES('Product: ' product.name)

Suppose textvar is a variable whose value is 'geog', which is the name of a dimension. Whether you enclose the word textvar in quotation marks determines whether the following OBJ function calls return the word VARIABLE (the type of object textvar is) or DIMENSION (the type of object geog is).

SHOW OBJ(TYPE 'textvar')
VARIABLE

SHOW OBJ(TYPE textvar)
DIMENSION

Working with Dates in Text Expressions

If you use a DATETIME value where a text value (TEXT, NTEXT, or ID) is expected, or if you store a DATETIME value in a text variable, then the DATETIME value is automatically converted to a text value.

The format of a DATETIME value is controlled by the NLS_DATE_FORMAT option. Once a DATETIME value is stored in a text variable, the NLS_DATE_FORMAT setting has no impact.

Working with NTEXT Data

TEXT and NTEXT data are interchangeable in most cases. However, implicit conversion can occur, such as when an NTEXT value is assigned to a TEXT variable. When TEXT is converted to NTEXT, no data loss occurs because the UTF-8 character encoding of the NTEXT data type encompasses most other data types. However, when NTEXT is converted to TEXT, data loss will occur if NTEXT characters are not represented in the workspace character set.

When TEXT and NTEXT values are used together, for example in a call to the JOINCHARS function, the TEXT value is converted to NTEXT and an NTEXT value is returned.