search cancel

CA DATACOM SQL RTRIM function not removing trailing blank characters

book

Article ID: 200165

calendar_today

Updated On:

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;    

 

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'

Environment

Release : 15.1

Component : CA DATACOM SQL

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