Table1
Column A | Column B |
---|---|
0001 | AMP^ |
0002 | JUN^ |
003 | RES^ |
Table2
Column A | Column B |
---|---|
AMY JONES | AMPLITUDE |
JUNE SMITH | JUNEBUG |
HARRY LOO | HAR^ |
I want to join these two tables on column B in both tables, but when I do a like nothing comes back and I think it is because of the ^
character and I'm not sure how to get around this.
SELECT
Table1.*,
Table2.*
FROM
Table1
LEFT JOIN
Table2 ON Table1.column_B LIKE CONCAT(Table2.column_B, '%')
This is what I want to get back:
Column A | Column B |
---|---|
AMY JONES | AMPLITUDE |
JUNE SMITH | JUNEBUG |
I do apoligize if this is sloppy but I am a new student to SQL so am not entirely versed on the ins and outs yet. Thank you in advance for your help
Wouldn't just join on the first three characters work for you?
SELECT
Table1.*,
Table2.*
FROM
Table1
LEFT JOIN
Table2 ON SUBSTR(Table1.column_B, 1, 3) = SUBSTR(Table2.column_B, 1, 3)