How to search string contains a comma separated bad words in database

Question

I want to search if a user submitted comment contains comma separated bad words in the database.

Table bad_word:

id	words
1	foo bar, fooBar
2	aaa bbb, ccc ddd

I'm using the following code but it only work for one bad word per row on database:

$comment = request('comment');
$bad_word_exists = \App\BadWord::whereRaw("LOCATE(words, ?) > 0", [$comment])->exists();

Expected output:

$comment	$bad_word_exists
foo	false
foo bar 123	true

Thanks

You need to normalise your data. Split all comma separated words into their own row in your `bad_word` table — apokryfos, Mar 22 '23 at 08:35
@apokryfos That's what I did at first. But client wants to enter multi words at once separated by commas... Finally I handled it on php. Not sure if it can be handled directly on mysql. — Wilson, Mar 22 '23 at 09:09
@Wilson Then split them up and insert them as multiple rows. — gre_gor, Mar 22 '23 at 18:28
@gre_gor But I need to restore the data for user to update. i.e.: record 1: `foo bar, fooBar`. record 2: `aaa bbb, ccc ddd` — Wilson, Mar 23 '23 at 01:07
@miken32 No. That question requires an exact match of the string to a comma-separated string. But I require the string to be partially matched. — Wilson, Mar 23 '23 at 01:14

score 4 · Accepted Answer · answered Mar 22 '23 at 09:10

First we convert the comma separated data into rows using CROSS JOIN JSON_TABLE() then we search using LOCATE() function

with cte as (
  SELECT t.id, trim(j.words) as words
  FROM bad_word t 
  CROSS JOIN JSON_TABLE(CONCAT('["', REPLACE(words, ',', '","'), '"]'),
                      '$[*]' COLUMNS (words TEXT PATH '$')) j
)
select *
from cte
where LOCATE(words, 'foo bar 123') > 0 ;

Demo here

score 0 · Answer 2 · answered Mar 22 '23 at 14:23

As stated by apokryfos, it would be better to normalise the values in your words column.

If your client wants to add multiple words as a comma separated list, you can still enter them into your table as separate words:

$inbound_words = 'some, new, bad, words';
$new_bad_words = array_map(fn($word) => ['words' => trim($word)], explode(',', $inbound_words));

// assuming unique index on words column so using insertOrIgnore
DB::table('bad_word')->insertOrIgnore($new_bad_words);

Thanks. But I need to restore the data for user to update. i.e.: record 1: `foo bar, fooBar`. record 2: `aaa bbb, ccc ddd` — Wilson, Mar 23 '23 at 01:07

How to search string contains a comma separated bad words in database

2 Answers2