从另一张表的另一列中的一列中搜索值 [英] Search the value from one column in another column in a different table

问题描述

我需要从一个表中搜索列值以及另一张表的列值.

I need to search the column value from one table with column value of another table.

例如

  MyTable
    Col1 Col2
    AAA   1
    BBB   2
    CCC   3

  MyTable2
    Col1          Col2
    GHKGH AAAh      1
    dhsjsBvd        2
    bdnd CCC b      3

我需要在MyTable2的col1值中从MyTable中搜索col1值.

I need to search the col1 value from MyTable in col1 value of MyTable2.

我不想对字符串进行硬编码，而是从表中获取值.

I dont want to hard code the string but take the value from table.

尝试使用instr和regex_instr，但是这些功能不允许搜索模式中的列值.

Tried using instr and regex_instr, but these functions don't allow column values in the pattern to search.

我正在使用oracle 10g. TIA

I am using oracle 10g. TIA

推荐答案

此处已测试示例: http: //sqlfiddle.com/#!4/037ffe/3

select 
  t1.col1 AS t1col1, t2.col1 AS t2col1
from 
  MyTable t1

  left join MyTable2 t2
  on t2.col1 like '%' || t1.col1 || '%'

---

包含DDL的完整示例:

---

Full example including DDL:

CREATE TABLE MyTable (col1 varchar2(9));

INSERT ALL 
    INTO MyTable (col1)
         VALUES ('AAA')
    INTO MyTable (col1)
         VALUES ('BBB')
    INTO MyTable (col1)
         VALUES ('CCC')
SELECT * FROM dual;

CREATE TABLE MyTable2 (col1 varchar2(18));

INSERT ALL 
    INTO MyTable2 (col1)
         VALUES ('GHKGH AAAh')
    INTO MyTable2 (col1)
         VALUES ('dhsjsBvd')
    INTO MyTable2 (col1)
         VALUES ('bdnd CCC b')
SELECT * FROM dual;

select 
  t1.col1 AS t1col1, t2.col1 AS t2col1
from 
  MyTable t1

  left join MyTable2 t2
  on t2.col1 like '%' || t1.col1 || '%'

结果集:

T1COL1    T2COL1
AAA       GHKGH AAAh
BBB       (null)
CCC       bdnd CCC b

这篇关于从另一张表的另一列中的一列中搜索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！