ASCII_STR scalar function

The ASCII_STR function returns an ASCII version of the string.

The schema is SYSIBM.

string-expression

An expression that returns a value of a built-in character or graphic string.

A character string must not be bit data (SQLSTATE 42815).

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

ASCII_STR returns an ASCII version of the string. Non-ASCII characters are converted to UTF-16 characters and appear in the result in the form of \xxxx (or \xxxx\yyyy for surrogate characters), where xxxx and yyyy represent a UTF-16 code unit. A backslash in a string-expression is converted to a double backslash.

The string unit of the result is OCTETS.

The length attribute of the result is MIN((5*n),32672), where n is determined as follows:

If a string-expression has the string units OCTETS or CODEUNITS16, n is the length attribute of the input string.
If a string-expression has the string units CODEUNITS32, n is twice the length attribute of the input string.

Result

The result of the function is a varying-length character string.

If the actual length of the result string exceeds the maximum for the return type, an error occurs(SQLSTATE 54006).

If the argument can be null, the result can be null. If the argument is null, the result is the null value.

Notes

As a syntax alternative, you can specify ASCIISTR as a synonym for ASCII_STR.

The following example returns the ASCII string equivalent of the Unicode (UTF-8) string, '4869206D616D6520697320D090D0BDD0B4D180D0B5D0B9202020F0908080':

SET :HV1 = ASCII_STR(X'4869206D616D6520697320D090D0BDD0B4D180D0B5D0B9202020F0908080');

:HV1 is assigned the value 'Hi, my name is \0410\043D\0434\0440\0435\0439 \D800\DC00'.

In this example, the UTF-8 characters D090, D0BD, D0B4, D180, D0B5, and D0B9 are converted to \0410\043D\0434\0440\0435\0439, and the non-ASCII character F0908080 is converted to \D800\DC00.

SET :HV1 = ASCII_STR('Hi, my name is Андрей(Andrei)');

:HV1 is assigned the value "Hi, my name is \0410\043D\0434\0440\0435\0439 (Andrei)"