MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `ads` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(500) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `ads` ADD PRIMARY KEY (`id`);
ALTER TABLE `ads` MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
INSERT INTO `ads` (`id`, `title`) VALUES (2, 'This PETS is very nice');
INSERT INTO `ads` (`id`, `title`) VALUES (3, 'This Cat is very nice');
INSERT INTO `ads` (`id`, `title`) VALUES (4, 'This dOG is very nice');
CREATE TABLE `ads_info` (
  `id` int(11) UNSIGNED NOT NULL,
  `infotext` varchar(500) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `tag` varchar(500) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `ads_info` ADD PRIMARY KEY (`id`);
ALTER TABLE `ads_info` MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
INSERT INTO `ads_info` (`id`, `infotext`, `tag`) VALUES (1, 'Dogs can eat a lot of things', 'dog, pets');
CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
create table my_temp_table (temp_columns varchar(100));
CREATE PROCEDURE ABC(in fullstr VARCHAR(255))

BEGIN
   
DECLARE a int default 0;
DECLARE str VARCHAR(255);
      
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,",",a);
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
        
         insert into my_temp_table values (str);
      END LOOP simple_loop;
END;
select * from my_temp_table;
call ABC((select tag from ads_info));
select * from my_temp_table;


temp_columns


dog

 pets


select concat('%', UPPER(trim(ads.temp_columns)), '%') from my_temp_table ads;


concat('%', UPPER(trim(ads.temp_columns)), '%')


%DOG%

%PETS%


Select * from ads B where exists
(select * from my_temp_table where 
find_in_set(UPPER(trim(temp_columns)), replace(UPPER(B.TITLE), ' ', ',')) > 0 );


id
title


2
This PETS is very nice

4
This dOG is very nice


SELECT * FROM ads, my_temp_table
WHERE find_in_set(UPPER(trim(temp_columns)), replace(UPPER(ads.TITLE), ' ', ',')) > 0 ;


id
title
temp_columns


2
This PETS is very nice
 pets

4
This dOG is very nice
dog