I have for example two tables
image and each record in these tables have a
text field, that may contain a bad word in it.
so the idea is, I want to check these records and change these bad words with a couple of stars (like ****) the number of stars is fixed and not dependant on words length.
and I have a bad words table called
so what I have so far is like this:
I have two trigger for each table (blog_post and image), one for update and one for insert. so the bad words will filtered out on insert or update. the content of each trigger is the same as below:
BEGIN DECLARE i INT; DECLARE s VARCHAR(1000); DECLARE r VARCHAR(1000); SET i = 1; SET s = ''; REPEAT SET s = ( SELECT REPLACE(split, COALESCE(slang, ''), '***') FROM (SELECT strSplit(new.text, ' ', i) AS split) s LEFT JOIN slang w ON s.split = w.slang LIMIT 1 ); SET r = CONCAT_WS(' ', r, s); SET i = i + 1; UNTIL s IS NULL END REPEAT; SET new.text = r; END
the strSplit procedure is defined like so (based on a StackOverflow or maybe a StackExchange question I don't remember it):
BEGIN DECLARE output VARCHAR(1000); SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos) , CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1) , delim , ''); IF output = '' THEN SET output = null; END IF; RETURN output; END
so I want to make a trigger to update the blog posts and images text field each time we add or edit a record from
slang table. so if I add
slang table, all images and blog posts that contain
someSlang in their
text field should get updated and place **** for each instance of
I'll appreciate any help and hints.