This one works however it gives multiple matches which is not expected. If the table 1 contains less than 20 characters then it will stay as it is and there is a match in table 2 for the same (i.e. purpose of cardio). The reason this is happening is because new server has length of field which is 20 characters and old server already had length which was 50 characters.

SELECT row_number() over(partition By T2.code_name ORDER BY T2.code) AS RW,T2.code_name As DesiredCodeName,T1.*

FROM dbo.table1 T1

INNER JOIN dbo.table2 T2

ON T1.code = T2.code

AND CHARINDEX(T1.code_name,t2.code_name) > 0

ORDER By T1.id,T1.code,RW