VARCHAR_FORMAT_BINARY scalar function

The VARCHAR_FORMAT_BINARY function returns a character string representation of a bit string that has been formatted using a format-string.

Read syntax diagramSkip visual syntax diagramVARCHAR_FORMAT_BINARY(expression ,format-string)

The schema is SYSIBM.

expression
An expression that returns a built-in binary string or character FOR BIT DATA string. The length of expression must be equal to the number of x or X characters in the format-string divided by 2.
format-string
An expression that returns a built-in character string or graphic string data type. The format-string variable contains a template for how the value for expression is to be formatted.

Valid format strings include: 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' and 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' (SQLSTATE 42815) where each x or X corresponds to one hexadecimal digit from the defined expression.

The result of the function is a varying-length character string with the length attribute and actual length based on the format string. For the two valid format strings, the length attribute and actual length of the result is 36. If either argument can be null, the result can be null; if either argument is null, the result is the null value.

Notes

VARCHAR_FORMAT_BIT is a synonym and syntax alternative for VARCHAR_FORMAT_BINARY.

Examples

  • Example 1: Represent a Universal Unique Identifier in its formatted form:
    VALUES VARCHAR_FORMAT_BINARY(BX'd83d6360181811db9804b622a1ef5492', 
                             'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
    Result returned:
    'd83d6360-1818-11db-9804-b622a1ef5492'
  • Example 2: Represent a Universal Unique Identifier in its formatted form: 
    VALUES VARCHAR_FORMAT_BINARY(BX'd83d6360181811db9804b622a1ef5492',
                             'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX')
    Result returned: 
    'D83D6360-1818-11DB-9804-B622A1EF5492'