Search code examples
phpmysqllaraveleloquent

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

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

Solution

  • 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