Search code examples
sqloracle

Is there a way to do a like join on two columns based on only the first 3 characters of one of the columns?

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

Solution

  • 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)