VARBINARY_FORMAT
The VARBINARY_FORMAT function returns a binary string representation of a character string that has been formatted using a 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:
Result returned:VALUES VARBINARY_FORMAT('d83d6360-1818-11db-9804-b622a1ef5492', 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
BX'D83D6360181811DB9804B622A1EF5492'
- Represent a Universal Unique Identifier in its binary form:
Result returned:VALUES VARBINARY_FORMAT('D83D6360-1818-11DB-9804-B622A1EF5492', 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX')
BX'D83D6360181811DB9804B622A1EF5492'
- Represent a string of hexadecimal characters in binary form:
Result returned:VALUES VARBINARY_FORMAT('ef01abC9')
BX'EF01ABC9'