How to Join Table with Different Length of ID

I have two columns that look like this :

Table_1

Table_2

tid	type	region	locatin	merk
00012345	PSW01	Jakarta I	JKT1-LANTAMAL	HYOSUNG
DTBA234	EDC	Jakarta I	JKT1-RKB BRI	HYOSUNG
00001234	PSW01	Jakarta I	JKT1-APOTIK KIMIA FARMA	HYOSUNG
EDC2345	EDC	Jakarta III	JKT1-KPU JAKARTA PUSAT	WINCOR
00000123	PSW01	Jakarta I	JKT-SPBU CIDENG	HYOSUNG

So i want to left join the table with this query :

SELECT *
FROM Table_1 AS t1
LEFT JOIN Table_2 AS t2
ON t1.Termid = CAST(t2.tid AS INT)

The query can run perfectly when I exclude the EDC type. But since I want to concatenate the whole line, I'm having an error like:

Conversion failed when converting the varchar value 'DTBA234' to data type int.

I know that the error is because there are characters other than numeric. But I don't know how to solve like the above case.

Can you help me?

Thank you.

** Note : Sorry for my english

Solution

You can extract the integer from the tid column of Table_2 and join with the termid column of Table_1.

Assuming the integer value in the tid column will be together, you can do the following:

SELECT *
FROM Table_1 AS t1
LEFT JOIN Table_2 AS t2
ON t1.Termid = CAST(SUBSTRING(t2.tid, PATINDEX('%[0-9]%', t2.tid), LEN(t2.tid)) AS INT)

NOTE: If tid has any value like EDC2345DBTA123, this won't work.