SQL Server : select all rows where Column does not contain any value from dynamic table of values

Question

I'm using SQL Server 2016. I am searching TableA and want it to not return any rows where one of the terms from TableB exists in a particular column of TableA.

Assume I have the following sample tables:

DECLARE @SearchTerms TABLE (word NVARCHAR(10))

INSERT INTO @SearchTerms
    SELECT
        v
    FROM 
        (VALUES ('ABC'), ('DEF')) vals(v)

SELECT * FROM @SearchTerms

DECLARE @MyStrings TABLE 
                   (
                        ID  INT,
                        string NVARCHAR(MAX)
                   )

INSERT INTO @MyStrings
    SELECT
        v, x
    FROM 
        (VALUES (1, 'This is the first sentence and has nothing'),
                (2, 'This is the second sentence and has ABC only'),
                (3, 'This is the third sentence and has DEF only'),
                (4, 'This is the fourth sentence and has ABC and DEF together')) vals(v,x)

SELECT * FROM @MyStrings

In table @SearchTerms, I have ABC and DEF. I want to select * from table @MyStrings where string value does not contain ABC or DEF.

Something like this:

SELECT * 
FROM @MyStrings
WHERE string NOT LIKE (SELECT word FROM @SearchTerms)

sticky bit sticky bit · Accepted Answer · 2019-01-22T23:59:00

If the search terms aren't nullable, you can left join the search terms using LIKE and filter for all rows, where the search term is null.

SELECT s.*
       FROM @mystrings s
            LEFT JOIN @searchterms t
                      ON s.string LIKE concat('%', t.word, '%')
       WHERE t.word IS NULL;

db<>fiddle

If they are nullable excluding them in the ON clause might work.

SELECT s.*
       FROM @mystrings s
            LEFT JOIN @searchterms t
                      ON s.string LIKE concat('%', t.word, '%')
                         AND t.word IS NOT NULL
       WHERE t.word IS NULL;

SQL Server : select all rows where Column does not contain any value from dynamic table of values

1 Answers