Start of change

VARBINARY_FORMAT

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

VARBINARY_FORMAT(expression,format-string)
expression
An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. A numeric or graphic argument is cast to a character string before evaluating the function. For more information about converting numeric or graphic to a character string, see VARCHAR.

All leading and trailing blanks are removed from expression before evaluating the function.

If a format-string is specified, the length of expression must be equal to the length of the format-string and the value of expression must conform to the template specified by the format-string. If a format-string is not specified, the value of expression (after removing leading and trailing blanks) should be an even number of characters from the ranges '0' to '9', 'a' to 'f', and 'A' to 'F'. If the length is an odd number of characters, the string is padded on the right with one '0' character.

format-string
An expression that returns a built-in character string or graphic string data type. format-string contains a template for how the value for expression is to be interpreted.

The valid format strings are: 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' and 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' where each 'x' or 'X' corresponds to one hexadecimal digit in the result. If 'X' is specified, the corresponding hexadecimal digit must not be a lower case character. If 'x' is specified, the corresponding hexadecimal digit must not be an upper case character.

The result of the function is a varying-length binary string. The length attribute of the result is half the length attribute of expression. If a format-string is not specified, the actual length is half the actual length of expression (after leading and trailing blanks have been removed and padding to an even number of characters). If a format-string is specified, the actual length is half the actual length of the format-string (after removing the non-digit separator characters). If either argument can be null, the result can be null; if either argument is null, the result is the null value.

Note

Syntax alternatives: HEXTORAW is a synonym for VARBINARY_FORMAT except that if the length of expression is an odd number of characters, the string is padded on the left with one '0' character. VARCHAR_BIT_FORMAT is a synonym for VARBINARY_FORMAT except that the result of the function is a varying-length character string FOR BIT DATA.

Example

  • Represent a Universal Unique Identifier in its binary form:
    VALUES VARBINARY_FORMAT('d83d6360-1818-11db-9804-b622a1ef5492', 
                       'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
    Result returned:
    BX'D83D6360181811DB9804B622A1EF5492'
  • Represent a Universal Unique Identifier in its binary form:
    VALUES VARBINARY_FORMAT('D83D6360-1818-11DB-9804-B622A1EF5492',
                       'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX')
    Result returned:
    BX'D83D6360181811DB9804B622A1EF5492'
  • Represent a string of hexadecimal characters in binary form:
    VALUES VARBINARY_FORMAT('ef01abC9')
    Result returned:
    BX'EF01ABC9'
End of change