CODEWITHSUNDEEP
sql-servert-sql
Vladimir
Vladimir

Reputation: 351

T-SQL: [column] not like [any value from another select]

I have table shop with an Email column.

Also I have another table Unsubscribed with a EmailPattern column.

How I can select only shops with Email column does not match any EmailPattern value from Unsubscribed table?

Example:

Unsubscribed has these records:

aaa
bbb
ccc

I expect result like:

select * 
from Shop 
where Email not like "%aaa%" 
  and Email not like "%bbb%" 
  and Email not like "%ccc%"

Upvotes: 3

Views: 4457

Answers (2)

laylarenee
laylarenee

Reputation: 3284

This will work:

-- setup test tables
CREATE TABLE #Shop (ID INT, Email VARCHAR(100));
CREATE TABLE #Unsubscribed (EmailPattern VARCHAR(100));

-- fill with example data
INSERT #Shop VALUES (1,'[email protected]');
INSERT #Shop VALUES (2,'[email protected]');
INSERT #Shop VALUES (3,'[email protected]');
INSERT #Shop VALUES (4,'[email protected]');
 -- general phrase
INSERT #Unsubscribed VALUES ('test');
 -- specific email
INSERT #Unsubscribed VALUES ('[email protected]');
--INSERT #Unsubscribed VALUES ('@'); -- watch out, this will match every email!
--INSERT #Unsubscribed VALUES ('_'); -- so will this

-- filter results:
-- This returns rows from Table A (Shop), when they do not match anything in table B (Unsubscribed)
SELECT
    A.*
FROM
    #Shop A
    LEFT JOIN #Unsubscribed B ON A.Email LIKE '%' + B.EmailPattern + '%'
WHERE
    B.EmailPattern IS NULL;

DROP TABLE #Shop;
DROP TABLE #Unsubscribed;

Be aware that if certain phrases or characters get into the unsubscribe pattern, It can potentially match every record!

Upvotes: 0

T I
T I

Reputation: 9933

Select *
From shop
Where not exists (
  Select 1
  From unsubscribed
  Where shop.email like '%' + unsubcribed.emailpattern + '%'
)

Upvotes: 5

Related Questions