通过断开字符串并比较每个单词来匹配两列 oracle sql 中的单词

Question

>我的表中有两列，例如column_A和column_B

假设column_A有数据"百思买卡信用"，column_B有"信用无买单"，在这里，我需要匹配两列中的单词并返回匹配单词的数量。在这种情况下，它应该返回 2 作为"买入"和"信用"匹配。任何人都可以建议sql代码做同样的事情。

请注意：column_a和column_b的大小不是固定的，即两者的字数可能会改变。

Answer 1

with t as (
SELECT 1 AS ID, 'best buy card credit' column_1,
       'credit no take buy order' column_2
  FROM DUAL
UNION ALL
SELECT 2 AS ID, 'gaurav is  fool' column_1, 'saurabh is fool' column_2
  FROM DUAL
           )
,t1_column1 as (
SELECT     ID, LEVEL AS n, REGEXP_SUBSTR (column_1, '[^ ]+', 1, LEVEL) AS val
      FROM t
CONNECT BY REGEXP_SUBSTR (column_1, '[^ ]+', 1, LEVEL) IS NOT NULL
        )
,t1_column2 as (
SELECT     ID, LEVEL AS n, REGEXP_SUBSTR (column_2, '[^ ]+', 1, LEVEL) AS val
      FROM t
CONNECT BY REGEXP_SUBSTR (column_2, '[^ ]+', 1, LEVEL) IS NOT NULL
        )
select id, LISTAGG(VAL,',') WITHIN GROUP(ORDER BY VAL ) words ,COUNT(*) "total matched words"
from
(
SELECT DISTINCT t1_column1.ID ID, t1_column1.val val
           FROM t1_column1, t1_column2
          WHERE t1_column1.ID = t1_column2.ID
            AND t1_column1.val = t1_column2.val
)
group by id