MySQL string contains string

Question

I am using MySQL Workbench and I'm currently looking for a function or something that helps me check if a string is within another string. For example:

text1 = 'hello , name , world';

If I have a string or varchar that is something like this

'My name is'

It should say that it contains since it has 'name' on it. Basically what I'm trying to do is I have a Comments table and I'm creating a trigger before insert on Comments that checks if the comment_text contains any swear words. These swear words are saved like this

select group_concat(word) into texto from reserved_words;

So it has all the words close to each other: swear1,swear2,swear3,etc

I want it to update a certain table if the comment really contains a swear word. I've tried to use LIKE, SUBSTR(),CONTAINS(),REGEXP without any success.

Can someone help?

Answer 1

LIKE is what you want. You need to wrap it with % to match anywhere in the string.

WHERE string LIKE '%name%'

will be true if the value of the string column contains the string name.

If you have the swear words in a column of a table, you can join them:

SELECT DISTINCT comment
FROM comments AS c
JOIN swear_words AS s ON c.comment LIKE CONCAT('%', s.word, '%')

Don't put the swear words into a comma-separated list, that makes it very difficult to work with them individually in MySQL.