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
So the result for column2 never used in column 1 would be
C
MA
The description of the problem talks about the string in column2. YOu can do this with some variation on like. In most databases, some variation of:
select t.*
from t
where t.column1 not like '%' || t.column2 || '%';
Some databases spell || as + or even concat(), but the idea is the same.
However, I'm not sure what the sample data is doing. In no case is the string in column2 in column1.
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
)
It returns:
Column2
-------
C
MA
YY
