SQL join question

Question

I have a table table_a with a column containing data as follows:

.aaa01932
.asd02343
.lkj03342

So it always starts with a period followed by 3 letters, followed by 2 number (01, 02 etc) followed by some extra numbers.

I have another table table_b with data as follows:

aaa01
asd02
lkj03

So it maps to the data in table_a but with the period and the extra numbers at the end omitted.

How do I select from table_a joining on table_b where the data in the shown columns are "equal". By equal I mean that table_a.the_column like '%table_b.the_column%'. So something like:

Select * from table_a 
  join table_b on (table_a.the_column like '%table_b.the_column%');

Except I don't know how to format that like clause to accept a column inside. Maybe I need to concatenate it in somehow?

Answer 1

The syntax for the LIKE would be:

table_a.the_column like '%' || table_b.the_column || '%'

An alternative would be SUBSTR:

table_b.the_column = substr(table_a.the_column,2,5)

As some comments have said, the SUBSTR is a better method for 2 reasons:

• It can be indexed:

  create index on table_a (substr(the_column,2,5));

• It is more accurate: you want to match only in that position, not anywhere in the column

Answer 2

Try this:

Select * from table_a 
  join table_b on (table_a.the_column like '%' || table_b.the_column || '%');

Answer 3

join table_b on substr(table_a.column,1) = table_b.column