ASCII_STR or ASCIISTR scalar function

The ASCII_STR function returns an ASCII version of the string in the system ASCII CCSID. The system ASCII CCSID is the SBCS ASCII CCSID on a MIXED=NO system or the MIXED ASCII CCSID on a MIXED=YES system.

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. string-expression must be an ASCII, EBCDIC, or Unicode string.

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. Start of change If the system ASCII CCSID does not support the backslash (\) character, string-expression must not contain Non-ASCII characters. End of change Non-ASCII characters are converted to UTF-16 characters and appear in the result in the form \xxxx (or \xxxx\yyyy for surrogate characters), where xxxx and yyyy represent a UTF-16 code unit.

The length attribute of the result will be MIN( ( 5*n),32704). Where n is the result of applying the formulas in Table 5 based on input and output data types.

The result of the function is a varying-length character string in the system ASCII CCSID. If the actual length of the result string exceeds the maximum for the return type, an error occurs.

The result can be null; if the argument is null, the result is the null value.

Example

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

SET :HV1 = 
ASCII_STR(X'48692C206D79206E616D6520697320D090D0BDD0B4D180D0B5D0B920F0908080');

: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. End of change

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)"