mysql使用regexp/substring在列表中选择 [英] mysql select in list with regexp/substring

问题描述

我有表A:

---CODE-----
|21XDS60020| <-There is somewhere in table B
|21XDS60021|
|21XDS60023| <-There is somewhere in table B
|21XDS60025|
|21XDS60026|

表B:

----------------DESCRIPTION--------------------------
|FAX21XDS60020[2008_504571][NMS]sdfg bla bla        |
|FAX21XDS52167[2008_XXX324][NMS]sdfg bla blb        |
|FAX21P025P61[2006_501909][21XDS60023]sdfg bla blc  |
|FAX21XDS60025[2006_502499][NMS]sdfg bla bld        |
|FAX21P0251296[2007_503659][NMS]sdfg bla ble        |

---

预期结果:

---

Expected Result:

---------------------DESCRIPTION--------------------
|FAX21XDS60020[2008_504571][NMS]sdfg bla bla       |
|FAX21P025P61[2006_501909][21XDS60023]sdfg bla blc |

我想从表B中选择所有描述"记录，如果它们包含表A的代码"记录之一作为子字符串的话 我不知道在这种情况下是否可以使用IN或EXISTS和REGEXP语句.

I want to select all 'description' records from table B, if they contain as substring one of the 'code' records of table A I don't know if I can use somehow IN or EXISTS and REGEXP statements in that case.

类似的东西(当然，以下是错误的):

Something like (of course the following is wrong) :

SELECT description FROM B WHERE description IN (select REGEXP(.*code.*) FROM A);

推荐答案

可能是与此相关的EXISTS:

SELECT description FROM B 
WHERE exists (
    select 1 FROM A
    where B.description like concat('%',A.code,'%') 
);

这篇关于mysql使用regexp/substring在列表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！