How can I check that the data in one column doesn't exist in another column

Question

I have a table Name with 4 columns:

ID|Forename|MiddleName|Surname

I want to pull out any rows which have the Forename somewhere in the MiddleName

E.g.

1|John|John|Smith

which I can do with select * from Name where Forename = MiddleName

but what I would like to do is pull out rows where the Forename is somewhere in the MiddleName

e.g.

2|John|John Michael|Smith

Any help would be much appreciated. Thank you

Answer 1

You can do things like this with the LIKE operator.
https://www.w3schools.com/sql/sql_like.asp
Here are very similar questions: SQL Join on a column LIKE another column, MySQL join query using like?
The solution is:
SELECT * FROM Name WHERE MiddleName LIKE '%' + Forename + '%'
or:
SELECT * FROM Name WHERE MiddleName LIKE CONCAT('%', Forename, '%')