I have two columns that look like this :

Table_1

Table_2

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

CodePudding user response：

As Sql Server doesnt support regexp_replace like other dbs theres no direct way but alternatively you can use SUBSTRING(id, Patindex...) like below reference :

How to find and remove alphabet letters in in a column on MS sql server

CodePudding user response：

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.