SQL Query (Like) Trying to find Titles with AND

Question

When doing a SQL Query and I want to pull up entries from a table that have AND in the name, but I do not want names that just have and in them.....

Confusing, but I'll show an example

2 Entries in the table : Pandomniam, and Frank and Beans.

When I do the query I just want Frank and Beans to come up, not Pandomniam. I am doing something like

SELECT NAME FROM TABLE WHERE NAME LIKE '%and%'

this will get me results, but I have uneeded ones.

My first instinct is to try and see if I can get just Pandomniam in a result so I could do an AND NOT LIKE to filter them out but I can't seem to come up with one.

Answer 1

SELECT NAME FROM TABLE WHERE NAME LIKE '% and %'

Answer 2

How about adding a couple spaces:

SELECT NAME FROM TABLE WHERE NAME LIKE '% and %'

Answer 3

SELECT NAME FROM TABLE
WHERE
NAME LIKE '% and %' OR 
NAME LIKE 'and %' OR 
NAME LIKE '% and'

Answer 4

You don't say what brand of RDBMS you're using. MySQL supports a regular-expression predicate RLIKE with a metacharacter sequence for "word boundary":

SELECT NAME FROM TABLE WHERE NAME RLIKE '[[:<:]]and[[:>:]]'

If you're using another brand of database other than MySQL, perhaps you can research its regular expression features.