需要将一个字段与另一个值上带有逗号的字段匹配

Question

我想匹配"FORMULATION"从表1到c_test_article_quot;从表2中，有多个用逗号分隔的公式。

表1:

+----------------+--------------------+
| SAMPLE_NUMBER  |    FORMULATION     | 
+----------------+-----------+--------+
|     84778      | S/200582/01-TA-002 |      
|     84777      | S/200582/01-TA-002 |
|     81691      | S/200451/01-TA-011 |
|     81690      | S/200451/01-TA-011 |
+----------------+-----------+--------+

表2

+-----------------------+--------------------------------------+------------------+
|                     C_TEST_ARTICLE                           | C_REPORT_NUMBER  | 
+----------------+-----------+---------------------------------+------------------+
|      S/200180/03-TA-001,S/200180/03-TA-002                   |       16698      |      
| S/200375/01-TA-001,S/200375/01-TA-002,S/200375/01-TA-003     |       15031      |
+--------------------------------------------------------------+------------------+

我想从这里得到的是，每个"C_TEST_ARTICLES"有一个"C_REPORT_NUMBER"，所以我想得到所有的" sample_number "因此，这样，我就有了与报告编号相关的样本。

Answer 1

你可以尝试使用LIKE

select SAMPLE_NUMBER  
from table1 
INNER JOIN table2 ON c_test_article like concat('%', formulation , '%'')

Answer 2

select
C_TEST_ARTICLE
,C_REPORT_NUMBER
,b1.SAMPLE_NUMBER
from TABLE 2
INNER JOIN TABLE 1 as b1 on C_TEST_ARTICLE like '%'+FORMULATION+'%'

Answer 3

Try

SELECT
T1.SampleNumber
,   T2.C_Report_Number
FROM    Table1  T1
,   Table2  T2
WHERE   CHARINDEX(T1.Formulation, T2.C_Test_article) > 0