This *might* give incorrect results (viz. empty result table), depending on the datatypes of the columns; the above solution only works when a.column1 is either of datatype VARCHAR(n) or CHAR(6), and a.column2 is of datatype VARCHAR(n) or CHAR(1).
Originally Posted by ARWinner
(Presuming that all data in your table is similar in width to what you example tells us ...)
Three alternative solutions (without the above restrictions):
(Now, the restriction being that b.column1 does not have embedded blanks...)
from a inner join b on b.column1 = rtrim(a.column1)||rtrim(column2)||column3
Finally, you may consider adding three (redundant) columns to table b, containing those substr()s; this might give a much more performant query (depending, of course, on the sizes of the two tables).
from a inner join b on
substr(b.column1,1,6) = a.column1 AND
substr(b.column1,7,1) = column2 AND
substr(b.column1,8,1) = column3
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting