How do I exclude rows in SQL where string does not contain two words

Question

Example table to reproduce:

CREATE TABLE example
    (name varchar(200)
     
    );

INSERT INTO example
(name)
VALUES
('ab'),
('ab|test'),
('test');

SQL query:

select
name
from example
where name not like '%ab%' and  name not like '%test%'

This returns 0

What I am looking to do is to first exclude any rows where the name column contains both 'ab' and 'test' and then return the rest. In this case it should return row 1 and row 3.

To add - in production the name column has around 300k records so I need to filter by exclusion instead of inclusion.

Answer 1

The operator LIKE as you use it, does not check if the pattern exists as a whole word in the column name. It could be part of the word.

Assuming that the words in the column name do not contain ',', you should use the function FIND_IN_SET() to safely check if a word exists in the column's value:

SELECT name
FROM example
WHERE NOT (
  FIND_IN_SET('ab', REPLACE(name, '|', ',')) 
  AND 
  FIND_IN_SET('test', REPLACE(name, '|', ','))
);

See the demo.

Answer 2

Use len() function

select e.* from example e where len(name) > 2