INTERPRET scalar function

Description for INTERPRET

expression

An expression that returns a built-in binary string or character FOR BIT DATA string. The length of expression must follow the rules in Table 1.

data-type

Specifies the data type of the result.

SMALLINT

For a small integer.

INTEGER or INT

For a large integer.

BIGINT

For a big integer.

CHARACTER(integer) or CHAR(integer)

CHARACTER or CHAR

For a fixed-length character string of length integer, which has a range 1–255. If the length specification is omitted, a length of 1 character is assumed.

VARCHAR(integer), CHAR VARYING(integer), or CHARACTER VARYING(integer)

For a varying-length character string of maximum length integer, which has a range 1–32704.

CCSID encoding scheme

Specifies the encoding scheme for the target data type. The specific CCSIDs for SBCS, BIT, and MIXED data are determined by the default CCSIDs for the server for the specified encoding scheme. The valid values are ASCII, EBCDIC, and UNICODE.

If the CCSID attribute is not specified for a character string and data-type is CHAR or VARCHAR, the encoding scheme is determined based on the following rules:

• If FOR BIT DATA is specified, a CCSID of 65535 is used.
• If the expression and data-type are both character strings, the encoding scheme of the result is the same as expression. The CCSID of the result is the appropriate CCSID for the encoding scheme and subtype of the result.
• If expression is binary and data-type is a character string, the encoding scheme and CCSID of the result depends on the context in which the INTERPRET function is specified, and the CCSID of the result is the appropriate CCSID for the encoding scheme and subtype of the result.
  • If the statement follows the rules that are described for type 1 statements in Determining the encoding scheme and CCSID of a string, the CCSID is determined as follows:
    • If the statement references a table or view, the encoding scheme of that table or view determines the encoding scheme for the result.
    • Otherwise, the encoding scheme is EBCDIC, the default EBCDIC CCSID is used, and the subtype depends on the MIXED DECP value.
  • Otherwise, the CCSID of the result is the appropriate CCSID for the application encoding scheme and subtype of the result.

FOR subtype DATA

Specifies a subtype for a character string with a data type of CHAR or VARCHAR. Do not use the FOR subtype DATA clause with any other data type. subtype can be one of the following keywords:

SBCS

The character string contains single-byte data.

MIXED

The character string contains mixed data. Do not specify MIXED if the value of MIXED DECP value is NO unless the CCSID UNICODE clause is also specified, or the character strings originates from a Unicode table space or database.

BIT

The character string contains holds BIT data. Only character strings are valid when subtype is BIT.

If you do not specify the FOR subtype DATA clause, the subtype depends on the encoding scheme of the result. If the encoding scheme of the result is not Unicode and the MIXED DECP value is NO, the subtype of the result is SBCS. Otherwise, the subtype of the result is mixed.

CCSID integer

Specifies that the target data type be encoded using the CCSID integer. The value must be one of the CCSID values in DECP. If the data-type is CHAR or VARCHAR, the CCSID specified must be either a SBCS, or MIXED CCSID, or 65535 for bit data. The encoding scheme of the result is determined from the numeric CCSID. For INTERPRET function, CCSID 367 refers to ASCII data.

Data type interpretation rules for INTERPRET

The following table describes the data the interpretation rules.