The BINARY function returns a fixed-length binary string representation of a string of any data type.

The schema is SYSIBM.

string-expression

An expression that returns a value of a character string, graphic string, or binary string data type.

integer

An integer constant value, which specifies the length attribute of the resulting BINARY data type.
The value must be 1 - 255.
If integer is not specified, the length attribute of the result is the lower of the following values:

• The maximum length for the BINARY data type
• The length attribute for the data type of string-expression expressed in bytes:
  • The length attribute, if string-expression is a binary string, a character string that is FOR BIT DATA, or a character string with string units of OCTETS
  • The length attribute multiplied by 2, if string-expression is a graphic string with string units of CODEUNITS16 or double bytes
  • The length attribute multiplied by 4, if string-expression is a character or graphic string with string units of CODEUNITS32

If string-expression is an empty string and the integer argument is not specified, an error is returned (SQLSTATE 42815).

The result of the function is a BINARY.
If the first argument can be null, the result can be null;
if the first argument is null, the result is the null value.

The actual length is the same as the length attribute of the result.
If the length of string-expression that is converted to a binary string is less than the length attribute of the result, the result is padded with hexadecimal zeros up to the length of the result.
If the length of string-expression that is converted to a binary string is greater than the length attribute of the result, truncation occurs.

A warning (SQLSTATE 01004) is returned in the following situations:

• The first argument is a character or graphic string (other than a CLOB or DBCLOB) and non-blank characters are truncated.
• The first argument is a binary string (other than BLOB) and non-hexadecimal zeros are truncated.

Examples

1. The following function returns a fixed-length binary string with a length attribute 1 and a value BX'00'.

        SELECT BINARY('',1)
        FROM   SYSIBM.SYSDUMMY1

2. The following function returns a fixed-length binary string with a length attribute 5 and a value BX'4B42480000'.

         SELECT BINARY('KBH',5)
         FROM   SYSIBM.SYSDUMMY1

3. The following function returns a fixed-length binary string with a length attribute 3 and a value BX'4B4248'.

        SELECT BINARY('KBH') 
        FROM   SYSIBM.SYSDUMMY1

4. The following function returns a fixed-length binary string with a length attribute 3 and a value BX'4B4248'.

        SELECT BINARY('KBH ',3) 
        FROM   SYSIBM.SYSDUMMY1

5. The following function returns a fixed-length binary string with a length attribute 3, a value BX'4B4248', and a warning (SQLSTATE 01004).

        SELECT BINARY('KBH 93',3) 
        FROM   SYSIBM.SYSDUMMY1