BINARY scalar function

The BINARY function returns a BINARY (fixed-length binary string) representation of a string of any type or of a row ID type.

Read syntax diagramSkip visual syntax diagramBINARY( string-expression,integer)

The schema is SYSIBM.

string-expression
An expression that returns a value that is a built-in character string, graphic string, binary string, or a row ID type.
integer
An integer value that specifies the length attribute of the resulting binary string. The value must be an integer in the range 1–255 inclusive.

If integer is not specified:

  • If the string-expression is the empty string constant, an error occurs
  • Otherwise, the length attribute of the result is the same as the length attribute of string-expression, except when the input is graphic data. In this case, the length attribute of the result is twice the length of string-expression.

The result of the function is a fixed-length binary string.

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 the string-expression is less than the length of the result, the result is padded with hexadecimal zeroes up to the length of the result. If the length of the string-expression is greater than the length attribute of the result, truncation is performed. A warning is returned unless the first input argument is a character string and all the truncated characters are blanks, or the first input argument is a graphic string and all the truncated characters are double-byte blanks, or the first input argument is a binary string and all the truncated bytes are hexadecimal zeroes.

Following examples assume EBCDIC encoding of the input string constants.

Example 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;
Example 2: The following function returns a fixed-length binary string with a length attribute 5 and a value BX'D2C2C80000'
   SELECT BINARY('KBH',5) 
     FROM SYSIBM.SYSDUMMY1;
Example 3: The following function returns a fixed-length binary string with a length attribute 3 and a value BX'D2C2C8'
   SELECT BINARY('KBH') 
     FROM SYSIBM.SYSDUMMY1;
Example 4: The following function returns a fixed-length binary string with a length attribute 3 and a value BX'D2C2C8'
   SELECT BINARY('KBH ',3) 
     FROM SYSIBM.SYSDUMMY1;
Example 5: The following function returns a fixed-length binary string with a length attribute 3 and a value BX'D2C2C8', a warning is also returned.
   SELECT BINARY('KBH 93',3) 
     FROM SYSIBM.SYSDUMMY1;
Example 6: The following function returns a fixed-length binary string with a length attribute 3 and a value BX'C1C2C3', a warning is also returned.
   SELECT BINARY(BINARY('ABC',5),3) 
     FROM SYSIBM.SYSDUMMY1;