如何加入，如果文本包含 [英] how to join, if the text contains

问题描述

我有 2 个表，假设表 A 有 10 行，表 B 有 100 行，我想执行连接，但匹配条件必须是 A 中的列类似于"B 中的列，这意味着任何东西都可以出现在 B 中的列之前或之后:

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:

例如:如果 A 中的列是 'foo'.如果 B 中的列是:'fooblah'、'somethingfooblah' 或只是 'foo'，则连接将匹配.我知道如何在标准的 like 语句中使用通配符，但在进行连接时很困惑.这有意义吗?谢谢.

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.

此代码不起作用:

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

示例:

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              |
+-------+------------------+

推荐答案

来自CONCAT 的 HANA 文档 看来 CONCAT() 只需要两个参数，而不是三个或更多.您可以通过嵌套两个对 CONCAT() 的调用来解决此问题:

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, '%'));

这篇关于如何加入，如果文本包含的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！