CODEWITHSUNDEEP
sqljoinnullibm-midrangetrim
ehh
ehh

Reputation: 3480

Trim null values in AS400

I am doing a join between two tables A and B on A.Item = B.Item. I am not getting the records as expected. After doing some investigations, I saw that all the items in table B contains nulls at the end of the item.

I would like to be able to do something like:

SELECT * FROM A INNER JOIN B ON TRIMNULL(A.ITEM) = TRIMNULL(B.ITEM);

Is there any such method in AS400 to trim the null values?

Upvotes: 1

Views: 529

Answers (1)

Charles
Charles

Reputation: 23793

Take a look at the TRIM function in the manual. You can specify a character to trim.

If assuming you mean a hex x'00' when you say NULL. Then this should work:

SELECT * 
FROM A INNER JOIN B 
    ON TRIM(TRAILING x'00' FROM A.ITEM) 
        = TRIM(TRAILING x'00' FROM B.ITEM);

Upvotes: 1

Related Questions