Question 15422 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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 = AppBadWord::whereRaw("LOCATE(words, ?) > 0", [$comment])->exists();

Expected output:

$comment $bad_word_exists
foo false
foo bar 123 true

Thanks

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search