INTERPRET
The INTERPRET function returns the first operand interpreted as the specified data-type.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
- string-expression
- An expression that returns a built-in binary string or character FOR BIT DATA string that contains the internal IBM® i representation of data-type. The length of string-expression must follow the rules in Table 1.
- data-type
- Specifies the data type of the result. For a description of data-type, see CREATE TABLE.
Result data type | Required string-expression length in bytes | Other considerations |
---|---|---|
SMALLINT | 2 | |
INTEGER | 4 | |
BIGINT | 8 | |
DECIMAL(p,s) | (p/2) + 1 | |
NUMERIC(p,s) | p | |
FLOAT (single precision) | 4 | |
FLOAT (double precision) | 8 | |
DECFLOAT(16) | 8 | |
DECFLOAT(34) | 16 | |
CHAR(n) | 0 to n | If the length of string-expression is less than n, the result is padded on the right with blanks. |
VARCHAR(n) | 2 to a maximum of n + 2 | The length represented by the first 2 bytes of string-expression cannot exceed n and must not be greater than the length of string-expression - 2. |
CLOB(n) | 4 to a maximum of n + 4 | The length represented by the first 4 bytes of string-expression cannot exceed n and must not be greater than the length of string-expression - 4. |
GRAPHIC(n) | 0 to 2n | The length of string-expression must be divisible by 2. If the length of string-expression is less than 2n, the result is padded on the right with blanks. |
VARGRAPHIC(n) | 2 to a maximum of 2n + 2 | The length of string-expression must be divisible by 2. The length represented by the first 2 bytes of string-expression cannot exceed n and must not be greater than the length of (string-expression - 2) / 2. |
DBCLOB(n) | 4 to a maximum of 2n + 4 | The length of string-expression must be divisible by 2. The length represented by the first 4 bytes of string-expression cannot exceed n and must not be greater than the length of (string-expression - 4) / 2. |
BINARY(n) | 0 to n | If the length of string-expression is less than n, the result is padded on the right with hexadecimal zeros. |
VARBINARY(n) | 2 to a maximum of n + 2 | The length represented by the first 2 bytes of string-expression cannot exceed n and must not be greater than the length of string-expression - 2. |
BLOB(n) | 4 to a maximum of n + 4 | The length represented by the first 4 bytes of string-expression cannot exceed n and must not be greater than the length of string-expression - 4. |
DATE | 4 | |
TIME | 3 | |
TIMESTAMP(p) | The integral part of ((p+1)/2) + 7 | |
XML | 4 to 2G | The length represented by the first 4 bytes of string-expression must not be greater than the length of string-expression - 4. |
Example
Function invocation | Result value |
---|---|
INTERPRET(BX'00000011' AS INTEGER) | 17 |
INTERPRET(BX'12345F' AS DECIMAL(5,2)) | 123.45 |
INTERPRET(BX'00258541' AS DATE) | 2020-04-05 |
INTERPRET(BX'616263' AS CHAR(3) CCSID 37) | /ÃÄ |
INTERPRET(BX'616263' AS CHAR(3) CCSID 1208) | abc |
INTERPRET(BX'0005C1C2C3C4C5' AS VARCHAR(5)) | ABCDE |
INTERPRET(BX'0003C1C2C3C4C5' AS VARCHAR(5)) | ABC |
INTERPRET(BX'0007C1C2C3C4C5' AS VARCHAR(7)) | Error |
INTERPRET(BX'00000003003100320033' AS DBCLOB(3) CCSID 1200) | 123 |