BLOB

The BLOB function returns a BLOB representation of a string of any type.

BLOB(string-expression,integer)
string-expression
A string-expression whose value can be a character string, graphic string, binary string, or row ID.
integer
An integer constant that specifies the length attribute for the resulting binary string. The value must be between 1 and 2 147 483 647.

If integer is not specified:

  • If the string-expression is the empty string constant, the length attribute of the result is 1.
  • Otherwise, the length attribute of the result is the same as the length attribute of the first argument, unless the argument is a graphic string. In this case, the length attribute of the result is twice the length attribute of the argument.

The actual length of the result is the minimum of the length attribute of the result and the actual length of the expression (or twice the length of the expression when the input is graphic data). If the length of the string-expression is greater than the length attribute of the result, truncation is performed. A warning (SQLSTATE 01004) 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.

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

Note

Syntax alternatives: The CAST specification should be used to increase the portability of applications when the length is specified. For more information, see CAST specification.

Example

  • The following function returns a BLOB for the string 'This is a BLOB'.
      SELECT BLOB('This is a BLOB')
        FROM SYSIBM.SYSDUMMY1
  • The following function returns a BLOB for the large object that is identified by locator myclob_locator.
      SELECT BLOB(:myclob_locator)
        FROM SYSIBM.SYSDUMMY1
  • Assume that a table has a BLOB column named TOPOGRAPHIC_MAP and a VARCHAR column named MAP_NAME. Locate any maps that contain the string 'Pellow Island' and return a single binary string with the map name concatenated in front of the actual map. The following function returns a BLOB for the large object that is identified by locator myclob_locator.
      SELECT BLOB( MAP_NAME CONCAT  ':  ' CONCAT  TOPOGRAPHIC_MAP )
        FROM ONTARIO_SERIES_4
        WHERE TOPOGRAPHIC_MAP LIKE '%Pellow Island%'