CA DATACOM SQL RTRIM function not removing trailing blank characters

Products

• Datacom
• DATACOM - AD
• Datacom/DB

Issue/Introduction

Trying to remove trailing blanks using the RTRIM function but it does not remove the trailing characters.

SELECT RTRIM(field_name) FROM table_name;    

 

Environment

Release : 15.1

Component : CA DATACOM SQL

Cause

RTRIM only removes blanks (X'40') characters.

Check that the trailing characters are actually blanks and not some other unprintable character.

Run the query using DBSQLPR and the HEXCHAR option. It will display the field in hex. 

In this case it was found the trailing characters are x'00'

Resolution

Use the TRIM function to remove characters other than blanks. To remove x'00' trailng characters code:

SELECT TRIM(TRAILING X'00' FROM field_name) FROM table_name;    

Additional Information

See the documentation on Character Functions