问题描述
之前可能有人问过这个问题,但我不知道如何搜索.我想找出 Column2 中的字符串是否是 Column1 的一部分,或者根本没有在 Column1 中使用
This may have been been asked before but I am not sure how to search for it. I want to find if the string in Column2 is a part of , or not used at all in Column1
Column1 | Column2 ======================= ABCDE + JKL | XC XC - PQ | A XYZ + A | C AC + PQ | MA
因此第 1 列中从未使用过的第 2 列的结果是
So the result for column2 never used in column 1 would be
C MA
推荐答案
似乎是另一个不允许正则表达式的正则表达式任务.
Seems like another regex expressions task with no regex allowed.
假设您的表达式只包含字母,您可以编写以下查询:
Assuming you have expressions containing only letters, you can write the following query:
CREATE TABLE Expressions ( Column1 varchar(20), Column2 varchar(20) ) INSERT Expressions VALUES ('ABCDE + JKL', 'XC'), ('XC - PQ', 'A'), ('XYZ + A', 'C'), ('AC + PQ', 'MA'), ('A+CF', 'ZZ'), ('BB+ZZ+CF', 'YY') SELECT E1.Column2 FROM Expressions E1 WHERE NOT EXISTS ( SELECT * FROM Expressions E2 WHERE E1.Column2=E2.Column1 --Exact match OR PATINDEX(E1.Column2+'[^A-Z]%', E2.Column1) <> 0 --Starts with OR PATINDEX('%[^A-Z]'+E1.Column2, E2.Column1) <> 0 --Ends with OR PATINDEX('%[^A-Z]'+E1.Column2+'[^A-Z]%', E2.Column1) <> 0 --In the middle )
它返回:
Column2 ------- C MA YY