Check if character column contains numeric value in DB/2 with sql

We have an AS400 that the db/2 is stored on and I am trying to join a table where character column contains the value of a numeric column.

I am running this in visual studio and I tried cast(val as char) and receive a 'as' not recognized, I tried CONVERT, and that isn't recognized either. Here is my original query:

SELECT        *
FROM           tab e, tab n
WHERE        (e.LN = 0001) AND (n.RMK LIKE '%' + e.ORDNO)

n.RMK contains a character value which is consistent but something like, "ordr 1401" and then e.ORDNO contains a numeric value which would be 1401

I want to see if e.RMK contains the 1401 value in e.ORDNO.

When I run that query I get a Errot type 6 = Numeric data that is not valid error. So I figure I would try and convert the numeric value to char and try again.

Like so,

SELECT        *
FROM           tab e, tab n
WHERE        (e.LN = 0001) AND  n.RMK  LIKE '%' + cast(e.ORDNO as  varchar(10))

This did not work,

I also tried

n.RMK LIKE '%' + CONVERT(VARCHAR(10), e.ORDNO) and I get CONVERT not recognized.

Solution

Finally got a working solution although it may not be the best:

 SELECT        *
 FROM           tab e, tab n
 WHERE        (e.LN = 0001) AND  (n.RMK LIKE CONCAT(RTRIM(CONCAT('%', CHAR(e.ORDNO))), '%'))

There was whitespace being added to the end of the e.ORDNO string for some reason using CHAR and there was whitespace at the end of the RMK string too; and the wildcard search wouldn't work unless I prepended and appended the '%' to e.ORDNO or I did a right trim on both n.RMK and e.ordno. Not sure if one way is better than the other.