CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016sql-server-2017
volume one
volume one

Reputation: 7563

How to use a SELECT statement in WHERE LIKE for multiple values

I am trying to filter out rows from a table where the [Description] column includes words from a list in another table.

I have got this far and then realised I don't know how to do this at all:

SELECT 
  p.Description  --- a large amount of text
FROM
  Products p
WHERE 
  p.Description NOT LIKE 
(SELECT List.Word FROM List) --- pseudo code

So I need something similar to NOT IN but its actually NOT LIKE instead.

Is this possible without me having to manually extract all the words in the list and do a NOT LIKE '%sofa%' AND NOT LIKE '%cushion%' AND NOT LIKE '%chair%' etc etc?

Upvotes: 3

Views: 735

Answers (2)

Vasily
Vasily

Reputation: 5782

additionally to posted solution, you can use the following variant:

SELECT p.Description
FROM   Products p
       LEFT JOIN (SELECT Test = '%'+Word+'%' FROM List) AS c
                 ON p.Description LIKE c.Test
WHERE  c.Test IS NULL;

test is here

Upvotes: 2

sticky bit
sticky bit

Reputation: 37472

You could check if no row in list exists, where the description is like the word of that row.

SELECT p.description
       FROM products p
       WHERE NOT EXISTS (SELECT *
                                FROM list l
                                WHERE p.description LIKE '%' + l.word + '%');

Upvotes: 7

Related Questions