How to check if a column contains a substring of string in SQL?

Question

I want to check if a column in my database contains words similar to my sample string. The opposite of

select * from myTable where name like '%words%';

so that if I have record with name=word I could retrieve it. With sample above I can only get result where words is a sub string of name column in myTable, therefore I cant get word

Answer 1

You just flip the two terms in your LIKE operator:

SELECT * 
FROM mytable
WHERE 'words' LIKE CONCAT('%',name,'%')

I believe that LOCATE() and INSTR() may work here too which looks nicer since there isn't a need for concatenating the search term/substring.

SELECT *
FROM mytable
WHERE INSTR('words', name) > 0