MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable1 (name CHAR(5), sys VARCHAR(16), id VARCHAR(16));
INSERT INTO mytable1 VALUES
('name1', 'aaa,bbb,ccc',     '8,3,5'),
('name2', 'ccc,bbb',         '22,33'),
('name3', 'ccc,ccc',         '11,56'),
('name4', 'aaa,ccc,ccc,ccc', '87,88,40,99'),
('name5', 'aaa,bbb',         '1,2');
SELECT * FROM mytable1;


name
sys
id


name1
aaa,bbb,ccc
8,3,5

name2
ccc,bbb
22,33

name3
ccc,ccc
11,56

name4
aaa,ccc,ccc,ccc
87,88,40,99

name5
aaa,bbb
1,2


SET @parameter:='ccc';
SELECT name, FIND_IN_SET(@parameter, sys) position
FROM mytable1
HAVING position > 0;


name
position


name1
3

name2
1

name3
1

name4
2


SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', FIND_IN_SET(@parameter, sys)), ',', -1) id
FROM mytable1
HAVING id > '';


id


5

22

11

88


SELECT DISTINCT name, SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', num), ',', -1) id
FROM mytable1
JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) numbers
    ON SUBSTRING_INDEX(SUBSTRING_INDEX(sys, ',', num), ',', -1) = @parameter
HAVING id NOT IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', FIND_IN_SET(@parameter, sys)), ',', -1)
                  FROM mytable1);


name
id


name3
56

name4
40

name4
99

name	sys	id
name1	aaa,bbb,ccc	8,3,5
name2	ccc,bbb	22,33
name3	ccc,ccc	11,56
name4	aaa,ccc,ccc,ccc	87,88,40,99
name5	aaa,bbb	1,2