VARCHAR_BIT_FORMAT scalar function

The VARCHAR_BIT_FORMAT function returns a bit string representation of a character string that has been formatted using a character template.

The schema is SYSIBM.

In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed.

character-expression: An expression that returns a value that is a built-in character string that is not a CLOB (SQLSTATE 42815). The required length is determined by the specified format string and how the value is interpreted. If a format-string argument is not specified, the length must be an even number of characters from the ranges '0' to '9', 'a' to 'f', and 'A' to 'F' (SQLSTATE 42815).
format-string: A character constant that contains a template for how the bytes of character-expression are to be interpreted.
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 in the result.

The result of the function is a varying-length character string FOR BIT DATA with the length attribute and actual length based on the format string. For the two valid format strings listed previously, the length attribute of the result is 36 and the actual length is 16. If a format-string argument is not specified, the length attribute of the result is half the length attribute of character-expression and the actual length is half the length of the actual length of character-expression. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.

Examples

Example 1: Represent a Universal Unique Identifier in its binary form:

VARCHAR_BIT_FORMAT('d83d6360-1818-11db-9804-b622a1ef5492', 
                   'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')

Result returned:

	x'D83D6360181811DB9804B622A1EF5492'

Example 2: Represent a Universal Unique Identifier in its binary form:

VARCHAR_BIT_FORMAT('D83D6360-1818-11DB-9804-B622A1EF5492',
                   'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX')

Result returned:

	x'D83D6360181811DB9804B622A1EF5492'

Example 3: Represent a string of hexadecimal characters in binary form.
```
VARCHAR_BIT_FORMAT('ef01abC9')
```
Result returned as a VARCHAR(4) FOR BIT DATA value:
```
	x'EF01ABC9'
```
Example 4: Represent a string of hexadecimal characters as a character string in the code page of the database. The result needs to be cast to a VARCHAR data type with the FOR MIXED DATA clause, assuming the database supports graphic types. Otherwise the result needs to be cast to a VARCHAR data type with the FOR SBCS clause. The following example assumes a Unicode database:
```
VALUES CAST(VARCHAR_BIT_FORMAT(HEX('abcdefg')) AS VARCHAR(10) FOR MIXED DATA) 
```
Result returned:
```
abcdefg
```