Search code examples
sqljoinnullibm-midrangetrim

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?

Solution

  • 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);