SQL - join 2 tables despite one column having an underscore-CodePudding

I am trying to combine 2 tables (Table A and Table B) based on 2 columns with the same data except there is a '_'.

For example:

ID1	ID2
IE03	IE_03
IE04	IE_04

Is there any way to join the 2 tables in SQL based on columns ID1 and ID2 regardless of the underscore?

CodePudding user response：

Yes; you'll have to remove the underscore. In Oracle, we use REPLACE function. For example:

SQL> select replace('IE_03', '_', '') result from dual;

RESULT
--------------------
IE03

SQL>

In other databases (I don't know which one you use as you didn't specify it), I believe they offer something similar.

So:

select *
from table_1 a join table_2 b on a.id1 = replace(b.id2, '_', '')

CodePudding user response：

I'm still learning SQL and I'ma be learning how to join tomorrow, hopefully. I'll help you then...