how to join, if the text contains

Question

I have 2 tables, say table A has 10 rows and table B has 100 rows and I want to perform a join, but the matching condition has to be where a column from A 'is like' a column from B meaning that anything can come before or after the column in B:

For example: if the column in A is 'foo'. Then the join would match if column in B is either: 'fooblah', 'somethingfooblah', or just 'foo'. I know how to use the wildcards in a standard like statement, but am confused when doing a join. Does this make sense? Thanks.

This Code doesn't work:

SELECT *
FROM TABLE a JOIN
     TABLE b
     ON b.column LIKE CONCAT('%', a.column, '%');

Example:

Table A
+-------+
| MYCOL | 
+-------+
| foo   |
| foo   |
| bar   |
| bbb   |
| bar   |
+-------+

Table B
+------------------+
| MYCOL            |
+------------------+
| fooblah          |
| somethingfooblah |
| foo              |
| barblah          |
| somethingbarblah |
| bar              |
+------------------+

Result:
+-------+------------------+
| MYCOL | MYCOL            |
+-------+------------------+
| foo   | fooblah          |
| foo   | somethingfooblah |
| foo   | foo              |
| --    | test             |
| bar   | somethingbarblah |
| bar   | bar              |
+-------+------------------+

Your code is correct SQL in any database that supports `CONCAT()`. Perhaps Hana SQL has specific limitations. — Gordon Linoff, Jun 11 '17 at 15:02
It's not clear from your question. Are you trying to join on table contents or on the actual field name ? — Michaël Roy, Jun 11 '17 at 15:03

score 4 · Answer 1 · answered Jun 11 '17 at 15:03

From the HANA documenation for CONCAT it appears that CONCAT() only takes two parameters, not three or more. You can workaround this by just nesting two calls to CONCAT():

SELECT *
FROM TABLE_a JOIN TABLE_b
    ON b.column LIKE CONCAT('%', CONCAT(a.column, '%'));

score 0 · Answer 2 · answered Jun 11 '17 at 16:01

0

   SELECT a.employee_id
       FROM employees a
         JOIN departments b
           ON a.department_id LIKE '%'
             ||a.department_id
             || '%';

answered Jun 11 '17 at 16:01

Kaushik Nayak

30,772
5
32
45

how to join, if the text contains

2 Answers2

Linked