A possible solution would be like this. I'm worried about performance though. Couldn't you correct the data in the first place?

CREATE TABLE #Table1( patid varchar(10))

CREATE TABLE #Table2( patid varchar(10))

INSERT INTO #Table1 VALUES('123453'),('124344ME'),('323390'),('3233MS')

INSERT INTO #Table2 VALUES('123453'),('124344'),('323390'),('3233')

SELECT *

FROM #Table1 a

JOIN #Table2 b ON CASE WHEN a.patid NOT LIKE '%[^0-9]%' THEN a.patid ELSE LEFT(a.patid, LEN(a.patid) - 2) END = b.patid

DROP TABLE #Table1

DROP TABLE #Table2