VARCHAR_FORMAT_BINARY scalar function

FL 508 The VARCHAR_FORMAT_BINARY function returns a character string representation of a bit string that has been formatted using a 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 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'

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.

The CCSID of the result is the CCSID of format-string. However, if format-string is a Unicode graphic string, the CCSID of the result is the character mixed CCSID that corresponds to the graphic argument.

Examples for VARCHAR_FORMAT_BINARY

The following statement represent a universally unique identifier (UUID) value in formatted form:
```
INSERT INTO EMP (ID)
VALUES (VARCHAR_FORMAT_BINARY(BX'd83d6360181811db9804b622a1ef5492', 
                             'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'));
```
The VARCHAR_FORAMT_BINARY call in the preceding statement returns the following result, with lowercase letters:
```
'd83d6360-1818-11db-9804-b622a1ef5492'
```

The following statement represents a UUID value in its formatted form:

INSERT INTO EMP (ID) 
VALUES (VARCHAR_FORMAT_BINARY(BX'd83d6360181811db9804b622a1ef5492', 
                             'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'));

The VARCHAR_FORAMT_BINARY call in the preceding statement returns the following result, with uppercase letters:


'D83D6360-1818-11DB-9804-B622A1EF5492'