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?

Can you put some example queries with data out here so we know what you tried? — Dale
– Dale, Commented Jun 23, 2018 at 2:25
Were you able to achieve the desired result ? Please see What should I do when someone answers my question?. — Amith Kumar
– Amith Kumar, Commented Jul 11, 2018 at 15:16

Barmar · Accepted Answer · 2018-06-23 02:14:30Z

12

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.

answered Jun 23, 2018 at 2:14

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-06-23 01:00:15Z

1

I would suggest:

where comment regexp replace(swearwords, ',', '|')

You may or may not want to include word delimiters in the regular expression pattern.

answered Jun 23, 2018 at 1:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Amith Kumar · Accepted Answer · 2018-06-23 05:20:23Z

0

Build your regex pattern from reserved_words table:

select CONCAT('(',group_concat(word SEPARATOR '|'),')') from reserved_words

And then run REGEXP check against the comment field.

DEMO RUN

create table reserved_words(word varchar(10));
insert into reserved_words values('hello'),('name'),('world');

select 'My name is' REGEXP (select CONCAT('(',group_concat(word SEPARATOR '|'),')') from reserved_words) swear
UNION ALL
select 'My title is' REGEXP (select CONCAT('(',group_concat(word SEPARATOR '|'),')') from reserved_words) swear
UNION ALL
select 'hello world' REGEXP (select CONCAT('(',group_concat(word SEPARATOR '|'),')') from reserved_words) swear;

RESULT

swear
1
0
1

edited Jun 23, 2018 at 5:20

answered Jun 23, 2018 at 2:37

Amith Kumar

4,9381 gold badge25 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL string contains string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related