VARBINARY_FORMAT scalar function

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

The schema is SYSIBM.

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 scalar function.

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'– '9', 'a'–'f', and 'A'–'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. The format-string argument contains a template for how the value for expression is to be formatted. Valid format strings have the following forms, where each 'x' or 'X' corresponds to one hexadecimal digit from the expression argument. If 'x' is specified, the character returned for the corresponding hexadecimal digit will be lower case. Otherwise, the character returned is upper case.

'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'

Syntax alternatives: 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.

Examples for VARBINARY_FORMAT

The following example statement represents a UUID value in binary form:

INSERT INTO EMP (ID) 
VALUES (VARBINARY_FORMAT('d83d6360-1818-11db-9804-b622a1ef5492',  
                   'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'));

The VARBINARY_FORMAT call in the preceding statement returns a result similar to the following value:

BX'D83D6360181811DB9804B622A1EF5492'

The following example statement represents a universally unique identifier value in binary form:
```
INSERT INTO EMP (ID)
VALUES (VARBINARY_FORMAT('D83D6360-1818-11DB-9804-B622A1EF5492',
                   'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'));
```
The VARBINARY_FORMAT call in the preceding statement returns a result similar to the following value:
```
BX'D83D6360181811DB9804B622A1EF5492'
```
The following statement represents a string of hexadecimal characters in binary form:
```
INSERT INTO EMP (ID)
VALUES (VARBINARY_FORMAT('ef01abC9'));
```
The VARBINARY_FORMAT call in the preceding statement returns a result similar to the following value:
```
BX'EF01ABC9'
```