Start of change

INTERPRET

The INTERPRET function returns the first operand interpreted as the specified data-type.

Read syntax diagramSkip visual syntax diagramINTERPRET( expression ASdata-type)
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)BINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKMG)DATETIME(0)TIMESTAMP(6)(integer)XMLccsid-clause
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDinteger

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.
If the CCSID attribute is not specified for a character or graphic string data type:
  • If data-type is CHAR, VARCHAR, or CLOB:
    • If FOR BIT DATA is specified, a CCSID of 65535 is used.
    • If FOR MIXED DATA is specified, the mixed-byte CCSID associated with the default CCSID of the job is used.
    • Otherwise, the single-byte CCSID associated with the default CCSID of the job is used.
  • If data-type is GRAPHIC, VARGRAPHIC, or DBCLOB, CCSID 1200 is used.
Table 1. Data type interpretation rules
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

Table 2.
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
End of change