CODEWITHSUNDEEP
sql
Ezra Polson
Ezra Polson

Reputation: 235

SQL join on two columns with different format

I have a column in TABLE1 with Numbers

      Id
      4-157-802 
      354-332
      33-989-083
      5-525-688

I have a column in TABLE2 with same numbers but without the dash (-)

      Id
      4157802 
      354332
      33989083
      5525688

I tried writing a join statement to join these tables based on these two columns as follows, its not working.

Select * from TABLE1
where
TABLE1.ID = TABLE2.ID
and TABLE2.ID in ('5525688')

Need help with this problem. Any help is appreciated.

Upvotes: 1

Views: 1538

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Select table1.* 
from TABLE1 join table2 
on replace(TABLE1.ID,'-','') = TABLE2.ID

You can try this using the replace function.

Upvotes: 3

Related Questions