ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Asterisk when concatenating binary column in SQL

book

Article ID: 227092

calendar_today

Updated On:

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

Cause

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

Environment

Release : All supported releases.
Component : IDMS SQL Option

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').