sql join issues with different data

Question

I want to join two tables with different data and data type is same.

In tableA the column col1 is with varchar datatype i.e. 123 and in tableB the column col1 is with varchar datatype i.e. ABC-123

Is there any way to join both columns by adding ABC as prefix to col1 in table 1 or by removing prefix ABC from col1 table 2.

Answer 1

You can use CONCAT(), as in:

select *
from table_a a
join table_b b on concat('ABC-', a.col1) = b.col2

This issue is quite common, specially in old databases, where you need to join VARCHAR columns with NUMERIC ones, since the designers back in the 90s though it that way.

Answer 2

Just use function CONCAT() at the ON clause of INNER JOIN

select * from
tableA a inner join tableB b
on CONCAT('ABC-', a.col1) = b.col2