• You just need to replace the .noc out on the join. ie..

    from t1

    join t2

    on t1.cola = replace(t2.cola,'.noc','')