By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE bad_word (
id int,
words varchar(50)
);
insert into bad_word values
(1, 'foo bar, fooBar'),
(2, 'aaa bbb, ccc ddd');
Records: 2 Duplicates: 0 Warnings: 0
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
| id | words |
|---|---|
| 1 | foo bar |
| 1 | fooBar |
| 2 | aaa bbb |
| 2 | ccc ddd |
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 ;
| id | words |
|---|---|
| 1 | foo bar |