Asterisk when concatenating binary column in SQL

Products

• IDMS

Issue/Introduction

A binary value in a SQL concatenation is returning an asterisk in the result.

SELECT 'A'|| X'31' || 'B' FROM SYSCA.SINGLETON_NULL;
*+
*+ (EXPR)
*+ ------
*+ A*B
*+
*+ 1 row processed

Environment

Release : All supported releases.
Component : IDMS SQL Option

Cause

The hexadecimal value x'31' is defined as non-displayable in the RHDCCODE table.

Resolution

This is working as documented. In a concatenation operation, binary data is considered as character data. This is documented at Comparison, Assignment, Arithmetic, and Concatenation Operations - Binary Values.

The character data is subject to validation according to what is specified in RHDCCODE and as such, the x'31' is converted to an asterisk (x'5C').